Using EXECUTE with a Character String
-
hi
,I need help from you guys, for using EXECUTE with a character string. Can you check if my script below is correct. I should have a string value and not a null value. Letters in red color, im concerned the most. If you have little time to analyze this please help me.
SET NOCOUNT ON
DECLARE @lvido nvarchar(125)
DECLARE @tbl sysname
DECLARE @lvid nvarchar(125)
DECLARE @sql varchar(1000)
SET @tbl=(SELECT lookupvalue FROM fieldtable WHERE fieldname='Department')
SET @lvid='FF8857D9-E7AE-4967-A73E-14CC4386F43F'
SET @sql=('SELECT ' + @lvido + '=lvid FROM ' + @tbl + ' WHERE lvid=' + @lvid + '')
EXEC(@sql)
SELECT @lvido
SET NOCOUNT OFF
Thank you very much in advance.
-
You never give @lvido a value, which means it is null. concat a null into a string and you get a null.
Regardless, I think what you're trying is not going to work. If you're trying to get whe value of @lvido back from the exec, then your approach isn't going to work. You need to look at sp_executesql. Try the following.
DECLARE @tbl sysname
DECLARE @lvid nvarchar(125)
SET @tbl=(SELECT lookupvalue FROM fieldtable WHERE fieldname='Department')
SET @lvid='FF8857D9-E7AE-4967-A73E-14CC4386F43F'
DECLARE @sql NVARCHAR(1000)
SELECT @sql = 'SELECT @lvido=lvid FROM ' + @tbl + ' WHERE lvid=' + @lvid
EXEC sp_executesql @sql, N'@LvidOut VARCHAR(50) OUTPUT', @LvidOut=@lvido OUTPUT
SELECT @LvidOut
HTH
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Beat me there... My solution:
SET NOCOUNT ON
DECLARE @lvido nvarchar(125)
DECLARE @tbl nvarchar(125)
DECLARE @sql nvarchar(1000)
SET @tbl='(SELECT lookupvalue FROM fieldtable WHERE fieldname=''Department'')'
exec sp_executesql @sql, N'@lvid nvarchar(125), @lvido nvarchar(125) output',
@lvid = 'FF8857D9-E7AE-4967-A73E-14CC4386F43F', @lvido = @lvido output
SELECT @lvido
SET NOCOUNT OFF
-
You seem to be missing the line where you declare the SQL string.
Yup, that'll work too, just a matter of where the variables get put in the string. Ordinarily, I'd prefer yours as it's more likely to cache and reues the execution plan, but since the table's variable here, reuse of execution plan's not that likely to happen.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Whoups
I guess it should have been
SET NOCOUNT ON
DECLARE @lvido nvarchar(125)
DECLARE @tbl nvarchar(125)
DECLARE @sql nvarchar(1000)
SET @tbl='(SELECT lookupvalue, lvid FROM fieldtable WHERE fieldname=''Department'')'
SELECT @sql = 'SELECT @lvido=lvid FROM ' + @tbl + ' t WHERE lvid=@lvid'
exec sp_executesql @sql, N'@lvid nvarchar(125), @lvido nvarchar(125) output',
@lvid = 'FF8857D9-E7AE-4967-A73E-14CC4386F43F', @lvido = @lvido output
SELECT @lvido
SET NOCOUNT OFF
-
i tried jesper's code, but still the result is null.
I want to get the value of @lvido from 'SELECT @lvido=lvid FROM ' + @tbl + ' WHERE lvid=' + @lvid' because i want check if @lvid exists from any @tbl.
Maybe you have some suggestions from my code below; if i run the code, the result is null.
SET NOCOUNT ON
DECLARE @lvido varchar(125)
DECLARE @tbl varchar(125)
DECLARE @lvid varchar(125)
DECLARE @sql nvarchar(125)
SET @tbl='uraccess_lookupvalues'
SET @lvid='FF8857D9-E7AE-4967-A73E-14CC4386F43F'
SET @sql=('SELECT ' + @lvido + '=lvid FROM ' + @tbl + ' WHERE lvid='+ @lvid + N'')
EXEC sp_executesql @sql, N'@lvido nvarchar(125) OUTPUT', @lvido OUTPUT
SELECT @lvido
SET NOCOUNT OFF
-
JESPER, YOUR LAST CODE WORKED!!!
. GILA MONSTER THANKS SO MUCH. WHERE ARE YOU GUYS FROM(COUNTRY)? JUST ASKIN. -
Your SQL string should read
SET @sql='SELECT @lvido=lvid FROM ' + @tbl + ' WHERE lvid=''' + @lvid + ''''
You want the variable to be part of the string that is executed, rather than the value (null) concatinated into the string.
The brackets also aren't necessary, and I don't know what the N was doing at the end.
You've also missed the end part of the sp_executesql command. With the string above it should read
EXEC sp_executesql @sql, N'@lvido nvarchar(125) OUTPUT', @lvido=@lvido OUTPUT
First parameter is the SQL string, second is the declaration of the variable you are passing in, third is the assignment of the variable.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
You're welcome. I'm from Denmark.
lviv must be a column in table @tbl, that may be why GilaMonster's code isn't working... Replacing
SELECT lookupvalue
by
SELECT lookupvalue, lviv
may fix the problem...
-
Glad to be of some assistance. I'm from South Africa
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass - , I have another case again where I need to use EXECUTE TO UPDATE certain table, and also to INSERT...
DECLARE @tbl nvarchar(125), @lookupvalue nvarchar(125), @lookuporder nvarchar(125), @lvid nvarchar(125)
I want to use those variable for UPDATE... using EXEC sp_executesql...
SET @sql=('UPDATE @tbl SET fieldname=@fieldname, lookupvalue=@lookupvalue, lookuporder=@lookuporder WHERE lvid=@lvid')
EXEC sp_executesql @sql, N'@lvid nvarchar(125), @lookupvalue nvarchar(125), @lookuporder numeric(9), @fieldname nvarchar(125), @tbl nvarchar(125)'....im not sure what code to fill here.
I will also use the same variables to INSERT to @tbl.
Need Help...
-
In this case, since you don't need to return a value, you can concat the string together and exec it.
SET @sql = 'Update ' + @tbl + ' Set .........
EXEC (@SQL)
Just remember to give all the variables values or the resulting string will be null.
I must say at this point that while dynamic sql may seem like a good solution, there are performance problems and major security vulnerabilities to using it.
Have a look through some of the older posts around here regarding dynamic sql. There's a article somewhere that goes very into depth about dynamic SQL. I don't have the url at the moment, will look for it later today
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Create one @sql for both operations:
SET @sql='UPDATE @tbl SET fieldname=@fieldname, lookupvalue=@lookupvalue, lookuporder=@lookuporder WHERE lvid=@lvid'
INSERT INTO @tbl (fieldname, lookupvalue, lookuporder, lvid)
SELECT @fieldname, @lookupvalue, @lookuporder, @lvid
WHERE NOT EXISTS (select * from @tbl where lvid=@lvid) '
and run it once with your set of parameters.
_____________
Code for TallyGenerator
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply