Wednesday, March 21, 2012
Dynamically use variables in SQL in EXECUTE
What I want to do is:
DECLARE @.sqlName varchar(255)
DECLARE @.temp NVARCHAR(100)
SET @.sqlName =(select name from master.dbo.sysdatabases where name like
'Job_%')
SET @.temp = 'USE ' + RTRIM(@.sqlName)
PRINT @.sqlName
EXEC (@.temp)
GO
--rest of my SQL code
--
Now basically I am going to have this script to run on multiple
databases where the database could be something different.
ex.
Computer1 - DB: Job_1234
Computer2 - DB: Job_5678
Before I run my code I want to make sure it runs under the correct
database. It finds the right database using select name from
master.dbo.sysdatabases where name like 'Job_%'
but how do I execute the USE @.temp statement. It says it executes, but
it still displays the master database in Query Analyzer. Any ideas on
how to do this? I just basically need to get this dynamic USE
statement to work. Thanks in advance.stuart.k...@.gmail.com wrote:
> Hi,
> What I want to do is:
> DECLARE @.sqlName varchar(255)
> DECLARE @.temp NVARCHAR(100)
> SET @.sqlName =(select name from master.dbo.sysdatabases where name like
> 'Job_%')
> SET @.temp = 'USE ' + RTRIM(@.sqlName)
> PRINT @.sqlName
> EXEC (@.temp)
> GO
> --rest of my SQL code
> --
> Now basically I am going to have this script to run on multiple
> databases where the database could be something different.
> ex.
> Computer1 - DB: Job_1234
> Computer2 - DB: Job_5678
> Before I run my code I want to make sure it runs under the correct
> database. It finds the right database using select name from
> master.dbo.sysdatabases where name like 'Job_%'
> but how do I execute the USE @.temp statement. It says it executes, but
> it still displays the master database in Query Analyzer. Any ideas on
> how to do this? I just basically need to get this dynamic USE
> statement to work. Thanks in advance.
Your code should work but the USE is scoped to the EXEC statement. Once
the EXEC is done you are returned to where you started. You need to put
some other code into the EXEC string as well if you want it to execute
in the context of another database.
EXEC is a pretty useless tool for this kind of thing. It's much easier
to parameterize the database in a connection string or at the OSQL
command prompt.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks a lot.
This worked if I ran something like
EXEC (@.temp + ' ' + @.code)
where @.code is the rest of my code that I wanted to run. I would use
OSQL if I could but unfortunately I can't.
Thanks again for your quick response.
-Stu
David Portas wrote:
> stuart.k...@.gmail.com wrote:
> Your code should work but the USE is scoped to the EXEC statement. Once
> the EXEC is done you are returned to where you started. You need to put
> some other code into the EXEC string as well if you want it to execute
> in the context of another database.
> EXEC is a pretty useless tool for this kind of thing. It's much easier
> to parameterize the database in a connection string or at the OSQL
> command prompt.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --|||You want to change context switching.
You can search sp_executeresultset on SQL Server 2000 SP3 later.
Not S2K5.
You can use below sample query.
DECLARE @.PROC NVARCHAR(4000)
SET @.PROC ='job_1234' + '.DBO.SP_EXECRESULTSET'
EXEC @.PROC @.SQLSTMT
"stuart.karp@.gmail.com"?? ??? ??:
> Hi,
> What I want to do is:
> DECLARE @.sqlName varchar(255)
> DECLARE @.temp NVARCHAR(100)
> SET @.sqlName =(select name from master.dbo.sysdatabases where name like
> 'Job_%')
> SET @.temp = 'USE ' + RTRIM(@.sqlName)
> PRINT @.sqlName
> EXEC (@.temp)
> GO
> --rest of my SQL code
> --
> Now basically I am going to have this script to run on multiple
> databases where the database could be something different.
> ex.
> Computer1 - DB: Job_1234
> Computer2 - DB: Job_5678
> Before I run my code I want to make sure it runs under the correct
> database. It finds the right database using select name from
> master.dbo.sysdatabases where name like 'Job_%'
> but how do I execute the USE @.temp statement. It says it executes, but
> it still displays the master database in Query Analyzer. Any ideas on
> how to do this? I just basically need to get this dynamic USE
> statement to work. Thanks in advance.
>
Wednesday, March 7, 2012
dynamical tablenames
I am about to create a procedure which will create tables with
dynamically specified tablenames.
Something like this
DECLARE tname CHAR(30);
SET tname = "1233321"; #or any string function
CREATE TABLE tname
(
#columns cpecification
);
But it doesn't works...Hi Ilya
You are not allowed to do this! If you can use temporary tables instead then
that would be a better solution. Failing that, read the following article
carefully http://www.sommarskog.se/dynamic_sql.html
John
"Ilya Dyoshin" wrote:
> Hi all!
> I am about to create a procedure which will create tables with
> dynamically specified tablenames.
> Something like this
> DECLARE tname CHAR(30);
> SET tname = "1233321"; #or any string function
> CREATE TABLE tname
> (
> #columns cpecification
> );
>
> But it doesn't works...
>
dynamic where prob
hi!
i can't seem to get this right...
create procedure PRstudentInfo
(
@.pStudentCode varchar(20) = NULL,
@.pHidden bit
)
as
begin
declare @.select varchar(max), @.where varchar(max)
set @.select =
'select b.name, c.studentcode from basicinfo b inner join fullinfo c on b.code = c.studentcode'
set @.where =
'WHERE isnull(c.studentcode,'''') ' + 'like ' + '''%' + isnull(@.pStudentCode,'') + '%''' + ' and ' +
' isnull(c.hidden,'''') = ''' + isnull(@.pHidden,'') '' --> here is the error
exec(@.select + @.where)
end
--
the error message is :
Incorrect syntax near ''
i also tried this:
' isnull(c.confidential,'''') = ''' + isnull(@.pConfidential,'') + ' '
The data types varchar and bit are incompatible in the add operator.
A tip : add the following code line:
print @.select + @.where , to see how sql string is made.
I suppose you have to put a space :
'hereWHERE isnull(c.studentcode,'''') etc
|||Change it to this:
' isnull(c.hidden,'''') = ''' + cast(isnull(@.pHidden,'') as char(1))
|||WOuld be nice if you can describe the next time, what you are trying to do within your code.Jens K. Suessmeyer
http://www.sqlserver2005.de
Sunday, February 26, 2012
DYNAMIC TSQL
Here is the sample query:
DECLARE @.TABLENAME NVARCHAR(50);
DECLARE @.COL NVARCHAR(50);
DECLARE @.VALUE NVARCHAR(50);
/*** THESE VARIABLES WERE ARE BEING DYNAMICALLY FEEDED THROUGH CURSOR***/
DECLARE @.SQL1 NVARCHAR(1000);
SET SQL1 = 'SELECT * FROM' + @.TABLENAME + ' WHERE' + @. COL + '=" + @.VALUE
EXECUTE sp_executesql @.SQL1
Now when I execute this SP it gives me error invalid colunm name. I figure out that its because of the variable @.VALUE. Eventually I found out that I need to single quote the value of this dynamically feeded variable @. VALUE
is there any way I can do this; give quotes to @.value like @. COL + '=" + ' @.VALUE'
Use the following query,
Code Snippet
DECLARE@.TABLENAME NVARCHAR(50);
DECLARE@.COL NVARCHAR(50);
DECLARE@.VALUE NVARCHAR(50);
DECLARE@.SQL1 NVARCHAR(1000);
DECLARE@.PARAM NVARCHAR(1000);
SET @.SQL1= N'SELECT * FROM ' + @.TABLENAME + N' WHERE ' + @.COL + N'=@.VALUE';
SET @.PARAM = N'@.Value as Nvarchar(50)';
EXECUTEsp_executesql @.SQL1, @.PARAM, @.VALUE
Friday, February 24, 2012
Dynamic SQL Statement for Cursor
I have a cursor that I need to pass a dynamic sql statement to that is in a
variable, is this possible?
e.g. DECLARE curOneRecord CURSOR FOR @.SQL
Thanks
BBoth cursors and dynamic SQL are considered bad practices. Anyhow, if you st
ill want to do this:
EXEC('DECLARE curOneRecord CURSOR FOR ' + SQL)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ben" <Ben@.Newsgroups.microsoft.com> wrote in message news:u8pL9ZkqFHA.904@.TK2MSFTNGP10.phx
.gbl...
> Hi
> I have a cursor that I need to pass a dynamic sql statement to that is in
a
> variable, is this possible?
> e.g. DECLARE curOneRecord CURSOR FOR @.SQL
> Thanks
> B
>|||Ben,
Do the contrary, open the cursor inside the dynamic sql statement.
Example:
declare @.c cursor
declare @.sql nvarchar(4000)
set @.sql = N'
set @.c = cursor for
select
orderid, customerid, orderdate
from
dbo.orders
where
customerid = @.customerid; open @.c'
exec sp_executesql @.sql, N'@.c cursor output, @.customerid nchar(5)', @.c
output, 'alfki'
if cursor_status('variable', '@.c') = 1
begin
while 1 = 1
begin
fetch next from @.c
if @.@.error != 0 or @.@.fetch_status != 0 break
end
end
if cursor_status('variable', '@.c') >= 0
close @.c
deallocate @.c
go
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
Did you try to find a set-based solution before using cursors?
AMB
"Ben" wrote:
> Hi
> I have a cursor that I need to pass a dynamic sql statement to that is in
a
> variable, is this possible?
> e.g. DECLARE curOneRecord CURSOR FOR @.SQL
> Thanks
> B
>
>|||an example ofr dynamic
sql statement. It might help
use northwind
declare @.test nvarchar(4000)
declare @.values nvarchar (200)
set @.values='1,2,3,4'
set @.test='select * from employees where employeeid in ('+ (@.values)+')'
EXEC(@.test)
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Ben" wrote:
> Hi
> I have a cursor that I need to pass a dynamic sql statement to that is in
a
> variable, is this possible?
> e.g. DECLARE curOneRecord CURSOR FOR @.SQL
> Thanks
> B
>
>
Sunday, February 19, 2012
dynamic sql using openrowset
can someone tell me why the upper part of the dynamic sql doesn't work while the lower does? i use openrowset to connect to Oracle. thanks!
declare @.abc varchar(10)
select @.abc = 'table20070921'
execute ('select a.* from OPENROWSET(''MSDAORA'',''SCHEME1'';''USER'';''PASSWORD'',
''select * from ' + @.abc + ' where flag = ''abc'') as a')
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'abc'.
Msg 105, Level 15, State 1, Line 2
Unclosed quotation mark after the character string ') as a'.
--
declare @.abc varchar(10)
select @.abc = 'table20070921'
execute ('select a.* from OPENROWSET(''MSDAORA'',''SCHEME1'';''USER'';''PASSWORD'',
''select * from ' + @.abc + ' where cust_num = 123456'') as a')
Joe,
It's hard to interpret error messages for statements you execute without ever looking at! If you substitute PRINT for execute in the upper part, you will be able to look at what you are trying to execute:
select a.* from OPENROWSET('MSDAORA','SCHEME1';'USER';'PASSWORD',
'select * from table20070 where flag = 'abc') as a
This isn't a well-formed string. Look at the openrowset sql parameter (the fifth parameter). The quoted string ends right before the abc, and that doesn't make sense.
You probably want this:
execute ('select a.* from OPENROWSET(''MSDAORA'',''SCHEME1'';''USER'';''PASSWORD'',
''select * from ' + @.abc + ' where flag = ''''abc'''') as a')
You need so many quotes because the literal string 'abc' appears doubly-nested in strings.
Please be careful when you create SQL with concatenation. If users are allowed to provide the parameters, you are basically letting users write parts of your SQL program, and there is a risk someone will try to insert malicious code. Look up SQL Injection on the web for more information.
Steve Kass
Drew University
http://www.stevekass.com
Dynamic SQL to populate a variable
Here's the WRONG way to do what I want. I need a way to populate a variable from the output of a dynamic query.
declare @.TableName sysname
set @.TableName = 'Customers'
delcare @.Output bigint
declare @.SQL varchar(max)
set @.SQL = 'select top 1 RowID from ' + @.TableName
select @.Output =
EXEC (@.SQL)
create function udf_GetDatabaseFingerPrint(
@.DBID bigint
)
begin
returns bigint
as
declare @.dbname sysname
, @.iBig bigint
, @.tSQL varchar(2000)
select @.dbName = Name from Master.Dbo.Sysdatabases where DBID = @.DBID
set @.tSQL = 'select sum(Rows) from ' + @.dbName + '.dbo.sysindexes'
set @.iBig = exec(@.tSQL)
return @.iBig
end
Number one, you cannot do this in a T-SQL function. Functions will not allow such things. You can use sp_executeSQL. In this case, something like this (and old example I had
declare @.objectId int,
exec sp_executeSQL
N'select @.objectId = max(object_id) from sys.objects',
N'@.objectId int output', @.objectId=@.objectId output
select @.objectId
Dynamic SQL Statement for Cursor
I have a cursor that I need to pass a dynamic sql statement to that is in a
variable, is this possible?
e.g. DECLARE curOneRecord CURSOR FOR @.SQL
Thanks
BBoth cursors and dynamic SQL are considered bad practices. Anyhow, if you st
ill want to do this:
EXEC('DECLARE curOneRecord CURSOR FOR ' + SQL)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ben" <Ben@.Newsgroups.microsoft.com> wrote in message news:u8pL9ZkqFHA.904@.TK2MSFTNGP10.phx
.gbl...
> Hi
> I have a cursor that I need to pass a dynamic sql statement to that is in
a
> variable, is this possible?
> e.g. DECLARE curOneRecord CURSOR FOR @.SQL
> Thanks
> B
>|||Ben,
Do the contrary, open the cursor inside the dynamic sql statement.
Example:
declare @.c cursor
declare @.sql nvarchar(4000)
set @.sql = N'
set @.c = cursor for
select
orderid, customerid, orderdate
from
dbo.orders
where
customerid = @.customerid; open @.c'
exec sp_executesql @.sql, N'@.c cursor output, @.customerid nchar(5)', @.c
output, 'alfki'
if cursor_status('variable', '@.c') = 1
begin
while 1 = 1
begin
fetch next from @.c
if @.@.error != 0 or @.@.fetch_status != 0 break
end
end
if cursor_status('variable', '@.c') >= 0
close @.c
deallocate @.c
go
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
Did you try to find a set-based solution before using cursors?
AMB
"Ben" wrote:
> Hi
> I have a cursor that I need to pass a dynamic sql statement to that is in
a
> variable, is this possible?
> e.g. DECLARE curOneRecord CURSOR FOR @.SQL
> Thanks
> B
>
>|||an example ofr dynamic
sql statement. It might help
use northwind
declare @.test nvarchar(4000)
declare @.values nvarchar (200)
set @.values='1,2,3,4'
set @.test='select * from employees where employeeid in ('+ (@.values)+')'
EXEC(@.test)
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Ben" wrote:
> Hi
> I have a cursor that I need to pass a dynamic sql statement to that is in
a
> variable, is this possible?
> e.g. DECLARE curOneRecord CURSOR FOR @.SQL
> Thanks
> B
>
>
Friday, February 17, 2012
Dynamic SQL statement
help appreciated.
Declare @.sqlStmt nvarchar(500)
Declare @.PatNo int
Declare @.Range int
set @.PatNO = 240
Set @.Range = 36000
--EXEC --('insert Into ' + @.TempVS +
SET @.sqlStmt = 'Select Distinct patNo, DateDrawn, Sort,TestnameDisplay,
Result, Units, abn, Range
FROM PatLabVal2 where PatNo = @.PatNo '
EXEC @.sqlstmt
I get an error :
Server: Msg 2812, Level 16, State 62, Line 11
Could not find stored procedure 'Select Distinct patNo, DateDrawn,
Sort,TestnameDisplay,
Result, Units, abn, Range
FROM PatLabVal2 where PatNo = @.PatNo '.
Thanks for the help,
Stephen K. MiyasatoDon't call Exec. Use sp_execsql instead. Look it up in books online for
examples, but you're about 90% of the way to implementing it.
Regards
Colin Dawson
www.cjdawson.com
"Stephen K. Miyasato" <miyasat@.flex.com> wrote in message
news:O5jeNtScGHA.3856@.TK2MSFTNGP03.phx.gbl...
> I'm still a newbie and can't get this dynamic SQL statement to work. Any
> help appreciated.
> Declare @.sqlStmt nvarchar(500)
> Declare @.PatNo int
> Declare @.Range int
> set @.PatNO = 240
> Set @.Range = 36000
> --EXEC --('insert Into ' + @.TempVS +
> SET @.sqlStmt = 'Select Distinct patNo, DateDrawn, Sort,TestnameDisplay,
> Result, Units, abn, Range
> FROM PatLabVal2 where PatNo = @.PatNo '
> EXEC @.sqlstmt
> I get an error :
> Server: Msg 2812, Level 16, State 62, Line 11
> Could not find stored procedure 'Select Distinct patNo, DateDrawn,
> Sort,TestnameDisplay,
> Result, Units, abn, Range
> FROM PatLabVal2 where PatNo = @.PatNo '.
> Thanks for the help,
> Stephen K. Miyasato
>|||Thanks for the help.
Stephen K. Miyasato
"Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
news:oH47g.65228$wl.53809@.text.news.blueyonder.co.uk...
> Don't call Exec. Use sp_execsql instead. Look it up in books online for
> examples, but you're about 90% of the way to implementing it.
> Regards
> Colin Dawson
> www.cjdawson.com
>
> "Stephen K. Miyasato" <miyasat@.flex.com> wrote in message
> news:O5jeNtScGHA.3856@.TK2MSFTNGP03.phx.gbl...
>|||Just adding to Colin's comment.
don't use nvarchar unless u are sure about unicode character. use varchar.
and
for executing dynamic sql
use
EXEC(@.sqlstmt)
again. This is just for the syntax.
Use sp_executesql. Its better in code reuse and does the same job from a
developers standpoint.
--
"Stephen K. Miyasato" wrote:
> I'm still a newbie and can't get this dynamic SQL statement to work. Any
> help appreciated.
> Declare @.sqlStmt nvarchar(500)
> Declare @.PatNo int
> Declare @.Range int
> set @.PatNO = 240
> Set @.Range = 36000
> --EXEC --('insert Into ' + @.TempVS +
> SET @.sqlStmt = 'Select Distinct patNo, DateDrawn, Sort,TestnameDisplay,
> Result, Units, abn, Range
> FROM PatLabVal2 where PatNo = @.PatNo '
> EXEC @.sqlstmt
> I get an error :
> Server: Msg 2812, Level 16, State 62, Line 11
> Could not find stored procedure 'Select Distinct patNo, DateDrawn,
> Sort,TestnameDisplay,
> Result, Units, abn, Range
> FROM PatLabVal2 where PatNo = @.PatNo '.
> Thanks for the help,
> Stephen K. Miyasato
>
>|||Stephen K. Miyasato (miyasat@.flex.com) writes:
> I'm still a newbie and can't get this dynamic SQL statement to work. Any
> help appreciated.
> Declare @.sqlStmt nvarchar(500)
> Declare @.PatNo int
> Declare @.Range int
> set @.PatNO = 240
> Set @.Range = 36000
> --EXEC --('insert Into ' + @.TempVS +
> SET @.sqlStmt = 'Select Distinct patNo, DateDrawn, Sort,TestnameDisplay,
> Result, Units, abn, Range
> FROM PatLabVal2 where PatNo = @.PatNo '
> EXEC @.sqlstmt
> I get an error :
> Server: Msg 2812, Level 16, State 62, Line 11
> Could not find stored procedure 'Select Distinct patNo, DateDrawn,
> Sort,TestnameDisplay,
> Result, Units, abn, Range
> FROM PatLabVal2 where PatNo = @.PatNo '.
You need parentheses after EXEC to execute dynamic SQL. Now you are
trying to execute a stored procedure of which the name is in @.sqlstmt.
Again, I recommend you to read my article
http://www.sommarskog.se/dynamic_sql.html. That will give you the
answer to this question, it will also sort out some problems that
you have run into yet. And you may even find out that you don't need
dynamic SQL at all.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Wednesday, February 15, 2012
Dynamic SQL error
Hello, Im trying to do dynamic SQL but get the following error with the SQL below
Must Declare the variable '@.TopRange'
can someone please help ?
Declare @.TopRange int
Declare @.BottomRange int
Declare @.SQL Varchar(1000)
Set @.TopRange = RTRIM(LEFT(REPLACE(@.param_leadage,'-',''),2))
Set @.BottomRange = LTRIM(RIGHT(REPLACE(@.param_leadage,'-',''),2))
SET @.SQL = 'SELECT dbo.tblCustomer.idStatus, dbo.tblCustomer.idCustomer, dbo.tblCustomer.DateSigned' +
' FROM dbo.tblCustomer' +
' WHERE DateDiff(day, dbo.tblCustomer.DateSigned, GetDate()) >= @.TopRange AND DateDiff(day,dbo.tblCustomer.DateSigned, GetDate()) <= @.BottomRange'
EXEC(@.SQL)
In your code, you have added the variable into the string. When the string is executed, the variable does not exist.
I think that you want to add the variable's value to the string, so you would need to set it outside the quotes and concatenate the variable value to the string.
Something like this:
Code Snippet
DECLARE
@.TopRange varchar(8),
@.BottomRange varchar(8),
@.SQL nvarchar(1000)
SELECT
@.TopRange = RTRIM(LEFT(REPLACE(@.param_leadage,'-',''),2)),
@.BottomRange = LTRIM(RIGHT(REPLACE(@.param_leadage,'-',''),2)),
@.SQL = 'SELECT ' +
'tc.idStatus, ' +
'tc.idCustomer, ' +
'tc.DateSigned ' +
'FROM dbo.tblCustomer tc' +
'WHERE datediff( day, tc.DateSigned, getdate()) >= ' + @.TopRange +
' AND datediff( day, tc.DateSigned, getdate()) <= ' + @.BottomRange
EXECUTE ( @.SQL )
Note that I changed the datatypes of the Top/Bottom Range variables -that is so they will concatenate with the string without having to be cast or converted.
Also, using the datediff() function this way in the criteria of the query will negate any possibility of using indexing. The query will require at best an index scan, and at worst, an entire table scan.