locked
Can parameter be passed to a stored procedure? RRS feed

  • Question

  • I am not sure.

    Code Snippet

    create PROCEDURE aaa

              @RegID varchar(50)

         as

         begin

    create table #TmpJnlData(
            dog varchar(50),
            cat varchar(50)

    )

     

    exec('insert #TmpJnlData
    select  (select top 1 dogID from dogsets where dogID=@RegID) as dog,
    .....

    end

     

     

    Then call it

    Code Snippet

    exec aaa 'FamousDog'

     

     

    Thanks
    Thursday, November 6, 2008 1:36 AM

Answers

All replies

  • EXEC can not be parameterized. Use sp_executesql. or you may concatenate the parameter value to the string and then execute

     

    exec('insert #TmpJnlData
    select  (select top 1 dogID from dogsets where dogID='+@RegID+') as dog,

     

    http://www.sommarskog.se/dynamic_sql.html

     

     

     

     or

     

     

     

     

    Madhu

    Thursday, November 6, 2008 1:55 AM
  • You should make a string then run SQLExecute for dynamic query.

     

    thanks

    Thursday, November 6, 2008 6:09 AM