sp_executesql won't return the expected result. Any help please?

  • Hello all,

    I have the following code part of an T-SQL process but I am stuck cause I don't get the expected result.

    Here is my code:

    USE tempdb

    DECLARE @jobname VARCHAR(255)

    DECLARE @profile VARCHAR(18)

    DECLARE @subjectdynamic VARCHAR(500)

    DECLARE @mailrecipients VARCHAR(110)

    DECLARE @sendmail TINYINT

    DECLARE @querysendmaildynamic NVARCHAR(500)

    DECLARE @droptable VARCHAR(500)

    SET @jobname = 'Austria test'

    SET @profile = 'SageSQL'

    SET @subjectdynamic = 'Consolidation Job ' + @jobname + ' is succesfully finished.'

    SET @querysendmaildynamic = 'SELECT TOP 1 flag FROM [' + @jobname + ']'

    SET @droptable = 'DROP TABLE [' + @jobname + ']'

    EXECUTE @sendmail = sp_executesql @querysendmaildynamic, N'@sendmailss TINYINT OUTPUT', @sendmailss=@sendmail OUTPUT

    select @sendmail;

    --EXEC @sendmail = sp_executesql @querysendmaildynamic, N'@sendmail TINYINT OUT', @sendmail OUT

    --select @sendmail AS flag

    --IF @sendmail = 1

    The command EXECUTE @sendmail = sp_executesql @querysendmaildynamic, N'@sendmailss TINYINT OUTPUT', @sendmailss=@sendmail OUTPUT

    returns value 1

    The problem is that the value is not passed correctly to the parameter @sendmail cause the statement right after select @sendmail; returns 0!!

    Any ideas how to fix this?

  • you are both asigning the variable to the true/false results, and expecting it to have a parameters OUTPUT; so for a picosecond the @sendmail has a value returned from the OUTPUT, but it is then overwritten but the rusults of the @sendmail = sp_executesql

    do something like this instead:

    ...--<-- previous code

    DECLARE @results int

    --EXECUTE @AnyVariable = sp_executesql assigns 0 if no errors.

    EXECUTE @results = sp_executesql @querysendmaildynamic, N'@sendmailss VARCHAR(11) OUTPUT', @sendmailss=@sendmail OUTPUT

    --OR

    --don't care about the return value?

    EXECUTE sp_executesql @querysendmaildynamic, N'@sendmailss VARCHAR(11) OUTPUT', @sendmailss=@sendmail OUTPUT

    PRINT @results

    PRINT @sendmail

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Btw, please note that the datatype is not VARCHAR(11) but TINYINT.

    Anyways, my point is not to print the value but to pass the value to the parameter.

    According to BOL this is possible.

    http://msdn.microsoft.com/en-us/library/ms188001.aspx


    DECLARE @IntVariable int;

    DECLARE @SQLString nvarchar(500);

    DECLARE @ParmDefinition nvarchar(500);

    DECLARE @max_title varchar(30);

    SET @IntVariable = 197;

    SET @SQLString = N'SELECT @max_titleOUT = max(JobTitle)

    FROM AdventureWorks2008R2.HumanResources.Employee

    WHERE BusinessEntityID = @level';

    SET @ParmDefinition = N'@level tinyint, @max_titleOUT varchar(30) OUTPUT';

    EXECUTE sp_executesql @SQLString, @ParmDefinition, @level = @IntVariable, @max_titleOUT=@max_title OUTPUT;

    SELECT @max_title;

    That's because the result of this parameter controls the execution flow. There is an IF..ELSE statement following, that depends on the value of this parameter.

    The possible values are 0 and 1.

    P.S. I correct the code in my initial post.

  • look at what you pasted from BOL: it is NOT doing EXECUTE @SomeVariable = sp_executesql

    that is where your error lies. you want to test the value of the OUTPUT parameter, not assign a value to it with the equals sign.

    don't do EXECUTE @sendmail = sp_executesql,

    do it this way:

    EXECUTE sp_executesql @querysendmaildynamic, N'@sendmailss VARCHAR(11) OUTPUT', @sendmailss=@sendmail OUTPUT

    IF @sendmail = [yourexpectedvalue]

    BEGIN

    END

    ELSE

    BEGIN

    END

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • This won't help neither. This doesn't work. 🙁

  • Ok, Ok. I just sorted it out.

    The problem was here:

    SET @querysendmaildynamic = N'SELECT @sendmailss = flag FROM [' + @jobname + ']'

    My code was:

    SET @querysendmaildynamic = N'SELECT flag FROM [' + @jobname + ']'

  • Anyone have a hint on how to include the "TOP x" function in this process?:

    DECLARE @SQL NVARCHAR(100),

    @params NVARCHAR(100),

    @data VARCHAR(100)

    SET @SQL = N'SELECT @data = TOP 1 CAST([Salary] AS VARCHAR(100)) FROM [dbo.JobDescriptions] WHERE [JobName] = ''CEO'''

    SET @params = N'@Data VARCHAR(100) OUTPUT'

    EXEC @SQL, @params,@Data = @data OUTPUT

    Thank You In Advance.

    JT

  • pretty much the same:

    DECLARE @SQL NVARCHAR(100),

    @params NVARCHAR(100),

    @data VARCHAR(100),

    @TopCount varchar(3)

    SET @SQL = N'SELECT @data = TOP ' + @TopCount + ' CAST([Salary] AS VARCHAR(100)) FROM [dbo.JobDescriptions] WHERE [JobName] = ''CEO'''

    SET @params = N'@Data VARCHAR(100) OUTPUT,@TopCount = ''50'' '

    EXEC @SQL @params,@Data = @data OUTPUT

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Because I get an error :

    Msg 156, Level 15, State 1, Line 1

    Incorrect syntax near the keyword 'TOP'.

  • Jason Tontz (9/15/2010)


    Because I get an error :

    Msg 156, Level 15, State 1, Line 1

    Incorrect syntax near the keyword 'TOP'.

    Add in a SET @Topcount = 1 between the declare and the rest of the code.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Jason Tontz (9/15/2010)


    Because I get an error :

    Msg 156, Level 15, State 1, Line 1

    Incorrect syntax near the keyword 'TOP'.

    That's because you have your TOP clause in the middle of your variable assignment. The TOP clause needs to come right after the SELECT.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • drew.allen (9/16/2010)


    Jason Tontz (9/15/2010)


    Because I get an error :

    Msg 156, Level 15, State 1, Line 1

    Incorrect syntax near the keyword 'TOP'.

    That's because you have your TOP clause in the middle of your variable assignment. The TOP clause needs to come right after the SELECT.

    Drew

    my bad;

    it should be more like this i think:

    DECLARE @SQL NVARCHAR(100),

    @params NVARCHAR(100),

    @data VARCHAR(100),

    @TopCount varchar(3)

    SET @SQL = N'SELECT TOP ' + @TopCount + ' @data = CAST([Salary] AS VARCHAR(100)) FROM [dbo.JobDescriptions] WHERE [JobName] = ''CEO'''

    SET @params = N'@Data VARCHAR(100) OUTPUT,@TopCount = ''50'' '

    EXEC @SQL @params,@Data = @data OUTPUT

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Thanks. That works perfectly. A little odd that the TOP statement is before the variable and not right before the column/ result list.

  • Jason Tontz (9/16/2010)


    Thanks. That works perfectly. A little odd that the TOP statement is before the variable and not right before the column/ result list.

    I don't find it at all odd. The TOP clause applies to the entire result set whereas the variable assignment applies to a single field in a single row (glossing over the quirky update). I find it odd that you expect to be able to intermingle global and local scopes willy-nilly.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

Viewing 14 posts - 1 through 13 (of 13 total)

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