Post reply

sp_executesql passing in params

  • September 13, 2004 at 10:03 am

    #83748

    Hi, I have this within an SP, The @CharID is passed into the SP and this is getting tghe value through.

    The code below however is telling me that i must declare the variable @acT, all the examples i have seen look like this code. Can anyone see what is wrong with it?

    Set @sql = 'select @mycount = count (ID) from CustomAudioTable WHERE acT = @acT'

    EXEC sp_executesql @sql, N'@mycount int output', @CustomCountALL out, N'@acT int', @acT = @CharID

    Thanks Dave

  • September 13, 2004 at 12:41 pm

    #522823

    I think the parameter syntax for sp_executesql just has you confused a little.  It *is* confusing at first, too!

    Here's an example of retrieving a count of user-defined tables in the current database.  I have chosen it as a starting point because it has two variables (like your statement) and can be run in any SQL Server database.

    declare @mycount int, @objtype char(1)
declare @SQL nvarchar(4000)
    set @objtype = 'U'
    Set @SQL = 'select @count = count(*) from sysobjects WHERE type = @type'
EXEC sp_executesql @SQL, N'@count int output, @type char(1)', @count=@mycount OUTPUT, @type=@objtype
    print @mycount

    Notice that the second sp_executesql parameter is a list of *all* declarations for variables referenced by @sql, inside a *single* set of quotation marks.  After that second parameter, you then supply mappings from each variable referenced by @sql to a corresponding local variable in the block of code that is calling sp_executesql.  In my case, I am mapping the @count variable that is referenced by @sql to the local variable @mycount (as well as marking it as OUTPUT) and also mapping the variable @type that is referenced by @sql to the local variable @objtype.  The variables referenced by @sql and the local variables could have the same names, by the way, but I wanted to use different names here to avoid ambiguity.

    Also, for the mapping parameters, it's OK to only reference the local variable, in which case I believe the parameters referenced by @sql are mapped to the local variables listed by order, I believe.  But I always do the explicit @var_in_sql_string=@local_var just to make sure I don't make any inadvertant mistakes there.

    Putting this together, I think that in your case you'd want to say something like this:

    EXEC sp_executesql @SQL, N'@mycount int output,@acT int', @CustomCountALL out, @acT = @CharID

    Hope this helps!

    Chris

  • September 14, 2004 at 3:28 am

    #522903

    Thanks thats a great help

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply