variables

Question

Hi All,

I have a procdeure that has the below piece of code. 

--create proc my_proc

--as

--other code

Set @Qry = 'Select col1, col2
            Into ##temptable  
            From mytable mt  
            Join myfunc(35) ft on ft.col1 = mt.col1  
           '     
Exec (@Qry)   

How can I make this dynamic. Thus, I dont want to use myfunc(35) all the time. 35 can be any number.

I want something like myfunc(@num) - where @num is a variable.

Please assist

Thanks

Answer 1

DECLARE @num INT=35
Set @Qry = 'Select col1, col2
            Into ##temptable 
            From mytable mt 
            Join myfunc('+CAST(@num AS varchar(20))+') ft on ft.col1 = mt.col1 
           '    
Exec(@Qry)

---

Ousama EL HOR

[If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]

 [User Page]     [MSDN Page]     [Blog]     [Linkedin]

Answer 2

Thanks much!!

Answer 3

declare @Qry  nvarchar(2000)=''
DECLARE @num INT=35
Set @Qry = N'Select col1, col2
            Into ##temptable   
            From mytable mt  
            Join myfunc( @num) ft on ft.col = mt.col1  
           '     
EXECUTE sp_executesql @Qry, N'@num int',@num