none
variables RRS feed

  • 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

    Friday, August 2, 2019 9:40 AM

Answers

  • 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 Answered "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]

    • Marked as answer by RonTech1 Friday, August 2, 2019 10:04 AM
    Friday, August 2, 2019 9:58 AM

All replies

  • 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 Answered "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]

    • Marked as answer by RonTech1 Friday, August 2, 2019 10:04 AM
    Friday, August 2, 2019 9:58 AM
  • Thanks much!!
    Friday, August 2, 2019 10:04 AM
  • 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


    Friday, August 2, 2019 1:41 PM
    Moderator