July 26, 2010 at 8:14 am
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?
July 26, 2010 at 8:40 am
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
July 26, 2010 at 8:51 am
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.
July 26, 2010 at 9:43 am
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
July 27, 2010 at 2:46 am
This won't help neither. This doesn't work. 🙁
July 27, 2010 at 2:58 am
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 + ']'
September 15, 2010 at 5:41 pm
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
September 15, 2010 at 5:52 pm
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
September 15, 2010 at 5:53 pm
Because I get an error :
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'TOP'.
September 15, 2010 at 5:55 pm
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.
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
September 16, 2010 at 6:15 am
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
September 16, 2010 at 6:28 am
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
September 16, 2010 at 9:55 am
Thanks. That works perfectly. A little odd that the TOP statement is before the variable and not right before the column/ result list.
September 16, 2010 at 10:45 am
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