Dynamic SQL Question
I'm trying to write a dynamic SQL statement and I can't get the syntax right. The statement is as follows:
declare @table nvarchar(50)
declare @sql nvarchar(500)
declare @lname nvarchar(50)
declare @return_var integer
set @table = 'table_names'
set @lname = 'smith'
set @return_var = 0
SET @SQL = "IF exists (SELECT 1 FROM "' + @table + '" WHERE fldlname = '" + @lname + "') SET '+@return_var+' = '+@return_var+' + 1"
exec(@SQL)
I know it's just a matter of escaping the variables correctly but I'm pulling my hair out on it.
Thanks in advance,
N
declare @table nvarchar(50)
declare @sql nvarchar(500)
declare @lname nvarchar(50)
declare @return_var integer
set @table = 'table_names'
set @lname = 'smith'
set @return_var = 0
SET @SQL = "IF exists (SELECT 1 FROM "' + @table + '" WHERE fldlname = '" + @lname + "') SET '+@return_var+' = '+@return_var+' + 1"
exec(@SQL)
I know it's just a matter of escaping the variables correctly but I'm pulling my hair out on it.
Thanks in advance,
N
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Dave,
That didn't work, there's an error about converting to type varchar
That didn't work, there's an error about converting to type varchar
ASKER
Bill,
Yeah, me again. This is the same procedure you've been helping me with. I'm running this check on six different tables and I just thought it would be cooler to pass in the table name than create six different procedures.
Yeah, me again. This is the same procedure you've been helping me with. I'm running this check on six different tables and I just thought it would be cooler to pass in the table name than create six different procedures.
ASKER
Bill
Accidentally submitted last comment before I was done. I got that to work in Query Analyzer but if I do this for each of the IF statements will it still be returning the value to the procedure:
CREATE PROCEDURE check_employee_dyn
@sql nvarchar(500),
@table varchar(50),
@lname varchar(50),
@fname varchar(50),
@return_var integer output
AS
BEGIN
SET @return_var = 0
*********changed only the first one**********
SET @SQL = N'IF exists (SELECT 1 FROM ' + @table + ' WHERE fldlname = ''' + @lname + ''') SET @return_var = @return_var + 1'
SET @vars = N'@return_var integer OUTPUT'
exec sp_executeSQL @SQL, @vars, @return_var OUTPUT
exec(@sql)
**************************
IF @return_var = 1
IF exists (select 1 from epls_proc_nonproc_individu
IF @return_var = 2
IF exists (select 1 from epls_proc_nonproc_individu
IF @return_var = 3
IF exists (select 1 from epls_proc_nonproc_individu
RETURN @return_var
END
GO
Accidentally submitted last comment before I was done. I got that to work in Query Analyzer but if I do this for each of the IF statements will it still be returning the value to the procedure:
CREATE PROCEDURE check_employee_dyn
@sql nvarchar(500),
@table varchar(50),
@lname varchar(50),
@fname varchar(50),
@return_var integer output
AS
BEGIN
SET @return_var = 0
*********changed only the first one**********
SET @SQL = N'IF exists (SELECT 1 FROM ' + @table + ' WHERE fldlname = ''' + @lname + ''') SET @return_var = @return_var + 1'
SET @vars = N'@return_var integer OUTPUT'
exec sp_executeSQL @SQL, @vars, @return_var OUTPUT
exec(@sql)
**************************
IF @return_var = 1
IF exists (select 1 from epls_proc_nonproc_individu
IF @return_var = 2
IF exists (select 1 from epls_proc_nonproc_individu
IF @return_var = 3
IF exists (select 1 from epls_proc_nonproc_individu
RETURN @return_var
END
GO
ASKER
It gets better, in the following statement:
IF exists (select 1 from epls_proc_nonproc_individu
I need to know how to escape the '%' as well. The three apostrophes didn't seem to work.
IF exists (select 1 from epls_proc_nonproc_individu
I need to know how to escape the '%' as well. The three apostrophes didn't seem to work.
Yep, it will return the value OK, as it is an OUTPUT type.
as for escaping the '%' you just need 2 single quotes each -
@SQL = N'IF exists (select 1 from epls_proc_nonproc_individu
the 3 in the other case were because, as well as having a simgle quote in the SQL that needed to be escaped, we also had a single quote which was the end of the string.
as for escaping the '%' you just need 2 single quotes each -
@SQL = N'IF exists (select 1 from epls_proc_nonproc_individu
the 3 in the other case were because, as well as having a simgle quote in the SQL that needed to be escaped, we also had a single quote which was the end of the string.
ASKER
Actually I got smart and just did the conversion before I passed it into the IF statements. I'm a little slow like that. Still debugging but so far it looks like it's on the UI side.
ASKER
Home stretch, here's what I have:
CREATE PROCEDURE check_employee_dyn
@table varchar(50),
@lname varchar(50),
@fname varchar(50),
@return_var integer output
AS
BEGIN
DECLARE @sql nvarchar(500)
DECLARE @vars nvarchar(500)
DECLARE @fname3 varchar(50)
DECLARE @fname4 varchar(50)
SET @return_var = 0
SET @fname3 = left(@fname,3)
SET @fname4 = left(@fname,4)
SET @SQL = N'IF exists (SELECT 1 FROM ' + @table + ' WHERE fldlname = ''' + @lname + ''') SET @return_var = @return_var + 1'
SET @vars = N'@return_var integer OUTPUT'
EXEC sp_executeSQL @SQL, @vars, @return_var OUTPUT
IF @return_var = 1
SET @SQL = N'IF exists (SELECT 1 FROM ' + @table + ' WHERE fldfname LIKE ''' + @fname3 + ''' AND fldlname = @lname) SET @return_var = @return_var + 1'
SET @vars = N'@return_var integer OUTPUT'
EXEC sp_executeSQL @SQL, @vars, @return_var OUTPUT
IF @return_var = 2
SET @SQL = N'IF exists (SELECT 1 FROM ' + @table + ' WHERE fldfname LIKE ''' + @fname4 + ''' AND fldlname = @lname) SET @return_var = @return_var + 1'
SET @vars = N'@return_var integer OUTPUT'
EXEC sp_executeSQL @SQL, @vars, @return_var OUTPUT
IF @return_var = 3
SET @SQL = N'IF exists (SELECT 1 FROM ' + @table + ' WHERE fldlname = ''' + @lname + ''' AND fldlname = @lname ) SET @return_var = @return_var + 1'
SET @vars = N'@return_var integer OUTPUT'
RETURN @return_var
END
GO
It validates as fine in Enterprise Manager but when I run the app I get a "(0x80040E14)[Microsoft][O
CREATE PROCEDURE check_employee_dyn
@table varchar(50),
@lname varchar(50),
@fname varchar(50),
@return_var integer output
AS
BEGIN
DECLARE @sql nvarchar(500)
DECLARE @vars nvarchar(500)
DECLARE @fname3 varchar(50)
DECLARE @fname4 varchar(50)
SET @return_var = 0
SET @fname3 = left(@fname,3)
SET @fname4 = left(@fname,4)
SET @SQL = N'IF exists (SELECT 1 FROM ' + @table + ' WHERE fldlname = ''' + @lname + ''') SET @return_var = @return_var + 1'
SET @vars = N'@return_var integer OUTPUT'
EXEC sp_executeSQL @SQL, @vars, @return_var OUTPUT
IF @return_var = 1
SET @SQL = N'IF exists (SELECT 1 FROM ' + @table + ' WHERE fldfname LIKE ''' + @fname3 + ''' AND fldlname = @lname) SET @return_var = @return_var + 1'
SET @vars = N'@return_var integer OUTPUT'
EXEC sp_executeSQL @SQL, @vars, @return_var OUTPUT
IF @return_var = 2
SET @SQL = N'IF exists (SELECT 1 FROM ' + @table + ' WHERE fldfname LIKE ''' + @fname4 + ''' AND fldlname = @lname) SET @return_var = @return_var + 1'
SET @vars = N'@return_var integer OUTPUT'
EXEC sp_executeSQL @SQL, @vars, @return_var OUTPUT
IF @return_var = 3
SET @SQL = N'IF exists (SELECT 1 FROM ' + @table + ' WHERE fldlname = ''' + @lname + ''' AND fldlname = @lname ) SET @return_var = @return_var + 1'
SET @vars = N'@return_var integer OUTPUT'
RETURN @return_var
END
GO
It validates as fine in Enterprise Manager but when I run the app I get a "(0x80040E14)[Microsoft][O
ASKER
What is the difference between:
@table varchar(50),
@lname varchar(50),
@fname varchar(50),
@return_var integer output
AND
DECLARE @sql nvarchar(500)
DECLARE @vars nvarchar(500)
DECLARE @fname3 varchar(50)
DECLARE @fname4 varchar(50)
Do you use the DECLARE statement to describe non-input variables? I'm a little confused. I'm passing in table, lname, fname, and return_var.
@table varchar(50),
@lname varchar(50),
@fname varchar(50),
@return_var integer output
AND
DECLARE @sql nvarchar(500)
DECLARE @vars nvarchar(500)
DECLARE @fname3 varchar(50)
DECLARE @fname4 varchar(50)
Do you use the DECLARE statement to describe non-input variables? I'm a little confused. I'm passing in table, lname, fname, and return_var.
ASKER
Figured out the error but not getting a return value.
ASKER
Here's the latest:
CREATE PROCEDURE check_employee_dyn
@table varchar(50),
@lname varchar(50),
@fname varchar(50),
@return_var integer output
AS
BEGIN
DECLARE @sql nvarchar(500)
DECLARE @vars nvarchar(500)
DECLARE @fname3 varchar(50)
DECLARE @fname4 varchar(50)
SET @return_var = 0
SET @fname3 = left(@fname,3)
SET @fname4 = left(@fname,4)
SET @SQL = N'IF exists (SELECT 1 FROM ' + @table + ' WHERE fldlname = ''' + @lname + ''') SET @return_var = @return_var + 1'
SET @vars = N'@return_var integer OUTPUT'
EXEC sp_executeSQL @SQL, @vars, @return_var OUTPUT
IF @return_var = 1
SET @SQL = N'IF exists (SELECT 1 FROM ' + @table + ' WHERE fldfname LIKE ''' + @fname3 + ''' AND fldlname = ''' + @lname + ''') SET @return_var = @return_var + 1'
SET @vars = N'@return_var integer OUTPUT'
EXEC sp_executeSQL @SQL, @vars, @return_var OUTPUT
IF @return_var = 2
SET @SQL = N'IF exists (SELECT 1 FROM ' + @table + ' WHERE fldfname LIKE ''' + @fname4 + ''' AND fldlname = ''' + @lname + ''') SET @return_var = @return_var + 1'
SET @vars = N'@return_var integer OUTPUT'
EXEC sp_executeSQL @SQL, @vars, @return_var OUTPUT
IF @return_var = 3
SET @SQL = N'IF exists (SELECT 1 FROM ' + @table + ' WHERE fldfname = ''' + @fname + ''' AND fldlname = ''' + @lname + ''' ) SET @return_var = @return_var + 1'
SET @vars = N'@return_var integer OUTPUT'
RETURN @return_var
END
GO
CREATE PROCEDURE check_employee_dyn
@table varchar(50),
@lname varchar(50),
@fname varchar(50),
@return_var integer output
AS
BEGIN
DECLARE @sql nvarchar(500)
DECLARE @vars nvarchar(500)
DECLARE @fname3 varchar(50)
DECLARE @fname4 varchar(50)
SET @return_var = 0
SET @fname3 = left(@fname,3)
SET @fname4 = left(@fname,4)
SET @SQL = N'IF exists (SELECT 1 FROM ' + @table + ' WHERE fldlname = ''' + @lname + ''') SET @return_var = @return_var + 1'
SET @vars = N'@return_var integer OUTPUT'
EXEC sp_executeSQL @SQL, @vars, @return_var OUTPUT
IF @return_var = 1
SET @SQL = N'IF exists (SELECT 1 FROM ' + @table + ' WHERE fldfname LIKE ''' + @fname3 + ''' AND fldlname = ''' + @lname + ''') SET @return_var = @return_var + 1'
SET @vars = N'@return_var integer OUTPUT'
EXEC sp_executeSQL @SQL, @vars, @return_var OUTPUT
IF @return_var = 2
SET @SQL = N'IF exists (SELECT 1 FROM ' + @table + ' WHERE fldfname LIKE ''' + @fname4 + ''' AND fldlname = ''' + @lname + ''') SET @return_var = @return_var + 1'
SET @vars = N'@return_var integer OUTPUT'
EXEC sp_executeSQL @SQL, @vars, @return_var OUTPUT
IF @return_var = 3
SET @SQL = N'IF exists (SELECT 1 FROM ' + @table + ' WHERE fldfname = ''' + @fname + ''' AND fldlname = ''' + @lname + ''' ) SET @return_var = @return_var + 1'
SET @vars = N'@return_var integer OUTPUT'
RETURN @return_var
END
GO
ASKER
Don't I need to escape the @return_var as well?
ASKER
I figured it out, lots of little dumb stuff. Thanks again Bill, you are the man.
N
N
See, yoy don't need any help at all - just leave you alone for a few hours and you solve it yourself!!
Just to confrim, what you said is exactly right - input parameters to a procedure do not use the keyword DECLARE, whereas any local variables do.
Just to confrim, what you said is exactly right - input parameters to a procedure do not use the keyword DECLARE, whereas any local variables do.
BillAn1 sir,
i am also facing the same problem .so instead of postinga new question i thought to ask it here only.
can you please tell what is the concept of using N' like N'@variablename which ihave seen at mant places.
secondly,
mine is a select query followed by an insert query
--------------------------
Set @sql = 'Select userName from Employee'
EXEC sp_execcutesql @sql,@user output
Set @sql = 'insert into user (userName) values '+ @user
EXEC sp_execcutesql @sql
--------------------------
now i want to store this @user into another table.how to do that as i am facing some problem related to scope of variable as i don't have any values in @user by the time my next line of code thatis insert query is executed
i am also facing the same problem .so instead of postinga new question i thought to ask it here only.
can you please tell what is the concept of using N' like N'@variablename which ihave seen at mant places.
secondly,
mine is a select query followed by an insert query
--------------------------
Set @sql = 'Select userName from Employee'
EXEC sp_execcutesql @sql,@user output
Set @sql = 'insert into user (userName) values '+ @user
EXEC sp_execcutesql @sql
--------------------------
now i want to store this @user into another table.how to do that as i am facing some problem related to scope of variable as i don't have any values in @user by the time my next line of code thatis insert query is executed
you really shouldn't do this, as you are supposed to post a new question, and assign points to 'pay' for the answer.
I will give you a brief answer, but if you need followup, you should post a new question.
the N before a string, e.g. N'this is a string' makes it a unicode (2 bytes per character) rather than an ascii (1 byte per character) string.
if you want to assign a variable inside dynamic SQL, then insert into a table using dynamic SQL again, you can do as follows :
declare @user varchar(32)
declare @sql nvarchar(1000)
declare @vars nvarchar(32)
set @vars = '@user varchar(32) output'
Set @sql = N'Select @user = userName from Employee'
EXEC sp_executesql @sql,@vars, @user output
select @user
Set @sql = 'insert into user (userName) values ('''+ @user + ''')'
print @sql
EXEC sp_executesql @sql
however, much simpler is not to use dynamicSQL at all :
declare @user varchar(32)
Select @user = userName from Employee
insert into user (userName) values (@user)
or even simpler again,
insert into user (userName)
select userName from Employee
now all of these solns will ahve problems if there is more than one record in the Employee table
I will give you a brief answer, but if you need followup, you should post a new question.
the N before a string, e.g. N'this is a string' makes it a unicode (2 bytes per character) rather than an ascii (1 byte per character) string.
if you want to assign a variable inside dynamic SQL, then insert into a table using dynamic SQL again, you can do as follows :
declare @user varchar(32)
declare @sql nvarchar(1000)
declare @vars nvarchar(32)
set @vars = '@user varchar(32) output'
Set @sql = N'Select @user = userName from Employee'
EXEC sp_executesql @sql,@vars, @user output
select @user
Set @sql = 'insert into user (userName) values ('''+ @user + ''')'
print @sql
EXEC sp_executesql @sql
however, much simpler is not to use dynamicSQL at all :
declare @user varchar(32)
Select @user = userName from Employee
insert into user (userName) values (@user)
or even simpler again,
insert into user (userName)
select userName from Employee
now all of these solns will ahve problems if there is more than one record in the Employee table
BillAn1 sir,
Thanks for the superior guidance. Sir i had already posted my query (with points) on Experts exchange but none of the database experts are able to answer my query.
But now i have seen a ray of hope in you. Please visit this to see th question.
https://www.experts-exchange.com/questions/21152064/problems-with-triggers.html
there is a lot of old explaination but you please see the last coment added by us there .
thanking you
eagerly waiting for reply
thanks in advance
Thanks for the superior guidance. Sir i had already posted my query (with points) on Experts exchange but none of the database experts are able to answer my query.
But now i have seen a ray of hope in you. Please visit this to see th question.
https://www.experts-exchange.com/questions/21152064/problems-with-triggers.html
there is a lot of old explaination but you please see the last coment added by us there .
thanking you
eagerly waiting for reply
thanks in advance
I have got some solution but the question still lies there.
As our query is quiet complicated we are unable to use the second method i.e. without using dynamic SQL which BillAn1 told in
https://www.experts-exchange.com/questions/21139349/Dynamic-SQL-Question.html. I have included my code below and the problem lies in commented section.
i want the select query in first line of commented section( *** marked) to return a value @new which i can in turn use in the next insert query
--------------------------
declare mycolumns2 cursor for
select column_name,data_type
from information_schema.columns
where
table_name = @table_name1 and
column_name <> 'IID' and
column_name not like 'fk%'
order by data_type
declare @column_name1 varchar(100), @data_type1 char(30)
open mycolumns2
fetch next from mycolumns2 into @column_name1,@data_type1
while @@fetch_status = 0
begin
**************************
execute('select'+ '(i.'+@column_name1 + ')'+' '+ 'from myinserted as i inner join mydeleted as d on'+' '+ 'i.'+@column_name1+ '=' +'d.'+ @column_name1+ ' '+
'where (i.'+ @column_name1 + '<> d.'+@column_name1+' '+ 'and not' + ' (i.'+@column_name1+' '+ 'is null and d.' + @column_name1+ ' '+ 'is null)'+')')
select @new from myinserted
**************************
fetch next from mycolumns2 into @column_name1,@data_type1
end
--------------------------
please tell if you need further explaination
thanks in advance
As our query is quiet complicated we are unable to use the second method i.e. without using dynamic SQL which BillAn1 told in
https://www.experts-exchange.com/questions/21139349/Dynamic-SQL-Question.html. I have included my code below and the problem lies in commented section.
i want the select query in first line of commented section( *** marked) to return a value @new which i can in turn use in the next insert query
--------------------------
declare mycolumns2 cursor for
select column_name,data_type
from information_schema.columns
where
table_name = @table_name1 and
column_name <> 'IID' and
column_name not like 'fk%'
order by data_type
declare @column_name1 varchar(100), @data_type1 char(30)
open mycolumns2
fetch next from mycolumns2 into @column_name1,@data_type1
while @@fetch_status = 0
begin
**************************
execute('select'+ '(i.'+@column_name1 + ')'+' '+ 'from myinserted as i inner join mydeleted as d on'+' '+ 'i.'+@column_name1+ '=' +'d.'+ @column_name1+ ' '+
'where (i.'+ @column_name1 + '<> d.'+@column_name1+' '+ 'and not' + ' (i.'+@column_name1+' '+ 'is null and d.' + @column_name1+ ' '+ 'is null)'+')')
select @new from myinserted
**************************
fetch next from mycolumns2 into @column_name1,@data_type1
end
--------------------------
please tell if you need further explaination
thanks in advance
comment added by login anugrah is also an id of
Softsolvers Technologies Pvt Ltd
please consider..........
thanks a lot
Softsolvers Technologies Pvt Ltd
please consider..........
thanks a lot
if i remove @param from sp_executesql and also remove "select @result = " from query string @sql ;
then ateast i can see the name of columns in query analyzer ,
althought use of @result is clear to return value but
please explain what is the use of this parameter .. i:e @param.
declare @SQL nvarchar(4000)
declare @param nvarchar(32)
declare @result nvarchar(100)
set @param = '@result varchar(100) output'
set @SQL = N'select '+ 'i.'+@column_name1 + '
from myinserted as i
inner join mydeleted as d on i.' + @column_name1 + ' = d.'+ @column_name1 + '
where (i.'+ @column_name1 + ' <> d.' + @column_name1 + '
and not (i.'+@column_name1+' '+ 'is null and d.' + @column_name1+ ' is null))'
exec sp_executeSQL @SQL, @result output
select @result
--------------------------
thanks.
then ateast i can see the name of columns in query analyzer ,
althought use of @result is clear to return value but
please explain what is the use of this parameter .. i:e @param.
declare @SQL nvarchar(4000)
declare @param nvarchar(32)
declare @result nvarchar(100)
set @param = '@result varchar(100) output'
set @SQL = N'select '+ 'i.'+@column_name1 + '
from myinserted as i
inner join mydeleted as d on i.' + @column_name1 + ' = d.'+ @column_name1 + '
where (i.'+ @column_name1 + ' <> d.' + @column_name1 + '
and not (i.'+@column_name1+' '+ 'is null and d.' + @column_name1+ ' is null))'
exec sp_executeSQL @SQL, @result output
select @result
--------------------------
thanks.
pls refer any questions to the open thread at
https://www.experts-exchange.com/questions/21152064/problems-with-triggers.html
https://www.experts-exchange.com/questions/21152064/problems-with-triggers.html
Anyway,
SET @SQL = 'IF exists (SELECT 1 FROM ' + @table + ' WHERE fldlname = ''' + @lname + ''') SET ' + @return_var + ' = ' + @return_var + ' + 1'
Should work.
NB. No double quotes there, all quotes are single quotes.
Dave Hilditch