Solved

Dynamic SQL Question

Posted on 2004-09-21
22
921 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:stretch73
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 5
  • 5
  • +1
22 Comments
 
LVL 17

Accepted Solution

by:
BillAn1 earned 200 total points
ID: 12114197
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
 
LVL 4

Expert Comment

by:davehilditch
ID: 12114220
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
 

Author Comment

by:stretch73
ID: 12114280
Dave,

That didn't work, there's an error about converting to type varchar
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

Author Comment

by:stretch73
ID: 12114292
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
 

Author Comment

by:stretch73
ID: 12114319
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
 

Author Comment

by:stretch73
ID: 12114385
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
 
LVL 17

Expert Comment

by:BillAn1
ID: 12114519
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
 

Author Comment

by:stretch73
ID: 12114571
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
 

Author Comment

by:stretch73
ID: 12114648
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
 

Author Comment

by:stretch73
ID: 12114702
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
 

Author Comment

by:stretch73
ID: 12114737
Figured out the error but not getting a return value.
0
 

Author Comment

by:stretch73
ID: 12114746
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
 

Author Comment

by:stretch73
ID: 12114981
Don't I need to escape the @return_var as well?
0
 

Author Comment

by:stretch73
ID: 12115723
I figured it out, lots of little dumb stuff.  Thanks again Bill, you are the man.

N
0
 
LVL 17

Expert Comment

by:BillAn1
ID: 12120854
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
 

Expert Comment

by:softsolvers
ID: 12296415
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
 
LVL 17

Expert Comment

by:BillAn1
ID: 12296606
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
 

Expert Comment

by:softsolvers
ID: 12296930
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
 

Expert Comment

by:softsolvers
ID: 12297038
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
 

Expert Comment

by:softsolvers
ID: 12297618
comment added by login anugrah is also an id of

Softsolvers Technologies Pvt Ltd

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

thanks a lot
0
 

Expert Comment

by:softsolvers
ID: 12297730
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
 
LVL 17

Expert Comment

by:BillAn1
ID: 12298091
pls refer any questions to the open thread at
http://www.experts-exchange.com/Databases/Q_21152064.html
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question