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
stretch73Asked:
Who is Participating?
 
BillAn1Commented:
problem 1 - the quotes should be like this :

SET @SQL = 'IF exists (SELECT 1 FROM ' + @table + ' WHERE fldlname = ''' + @lname + ''') SET ' +@return_var+' = '+@return_var+' + 1'

problem 2 - @return_var is an integer variable, not a dynamic name, so what you actually want is not to have it dynamicly evaluated
You would actually want
SET @SQL = 'IF exists (SELECT 1 FROM ' + @table + ' WHERE fldlname = ''' + @lname + ''') SET @return_var = @return_var + 1'

problem 3 is that you cannot access variables within an exec (@SQL) type of structure - they are local variables, and their scope does not extend to within the called procedure. Instead, you will need to use sp_executeSQL, and OUTPUT type variables.
you can do something like this :


declare @table nvarchar(50)
declare @sql nvarchar(500)
declare @vars nvarchar(500)
declare @lname nvarchar(50)
declare @return_var integer

set @table = 'table_names'
set @lname = 'smith'
set @return_var = 0


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



0
 
davehilditchCommented:
Well, what I do is always use single quotes as delimiters, and then if you need to put a single quote inside a string/varchar variable then you have to repeat the single quote.  NB this makes 2 single quotes which looks like double quotes but are very different.

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

0
 
stretch73Author Commented:
Dave,

That didn't work, there's an error about converting to type varchar
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
stretch73Author Commented:
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.  
0
 
stretch73Author Commented:
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_individual WHERE fldfname LIKE left(@fname, 3) + '%' AND fldlname = @lname) SET @return_var = @return_var + 1

IF @return_var  = 2

IF exists (select 1 from epls_proc_nonproc_individual WHERE fldfname LIKE left(@fname, 4) + '%' AND fldlname = @lname) SET @return_var = @return_var + 1

IF @return_var  = 3

IF exists (select 1 from epls_proc_nonproc_individual WHERE fldfname = @fname AND fldlname = @lname ) SET @return_var = @return_var + 1


RETURN @return_var

END
GO
0
 
stretch73Author Commented:
It gets better, in the following statement:

IF exists (select 1 from epls_proc_nonproc_individual WHERE fldfname LIKE left(@fname, 3) + '%' AND fldlname = @lname) SET @return_var = @return_var + 1

I need to know how to escape the '%' as well.  The three apostrophes didn't seem to work.
0
 
BillAn1Commented:
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_individual WHERE fldfname LIKE left(@fname, 3) + ''%'' AND fldlname = @lname) SET @return_var = @return_var + 1'

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.

0
 
stretch73Author Commented:
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.
0
 
stretch73Author Commented:
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][ODBC SQL Server Driver][SQL Server]Must declare the variable '@lname'." error.  
0
 
stretch73Author Commented:
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.
0
 
stretch73Author Commented:
Figured out the error but not getting a return value.
0
 
stretch73Author Commented:
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
0
 
stretch73Author Commented:
Don't I need to escape the @return_var as well?
0
 
stretch73Author Commented:
I figured it out, lots of little dumb stuff.  Thanks again Bill, you are the man.

N
0
 
BillAn1Commented:
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.
0
 
softsolversCommented:
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
0
 
BillAn1Commented:
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
0
 
softsolversCommented:
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.

http://www.experts-exchange.com/Databases/Q_21152064.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

0
 
softsolversCommented:
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
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21139349.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
0
 
softsolversCommented:
comment added by login anugrah is also an id of

Softsolvers Technologies Pvt Ltd

please consider..........

thanks a lot
0
 
softsolversCommented:
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.
0
 
BillAn1Commented:
pls refer any questions to the open thread at
http://www.experts-exchange.com/Databases/Q_21152064.html
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.