|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Dynamic WHERE & Datesequals 1. I need the @timeID equals 2 part to return records between @dtStartDate and @dtEndDate. (I just set the enddate equal to maxdate to keep my example simple). In the below section where @timeID = 2, I'm getting a syntax error. Can anyone help with the part below that tests IF @timeID = 2 ? I just need some quote help with that part of the where clause. This runs on Northwind. CODE ******************************************* declare @SQL varchar(1000), @typeID int, @timeID int declare @dtStartDate datetime, @dtEndDate datetime, @dtMaxDate datetime set @typeID = 1 SET @timeID = 1 set @dtMaxDate = (SELECT MAX(OrderDate) FROM Orders) set @dtStartDate = (SELECT DATEADD(day, -20, @dtMaxDate)) set @dtEndDate = @dtMaxDate SET @SQL = 'SELECT Orders.OrderDate, ' IF @typeID = 1 SET @SQL = @SQL + 'SUM(Orders.Freight) AS SumOfFreight ' IF @typeID = 2 SET @SQL = @SQL + 'COUNT(*) AS SalesCount ' SET @SQL = @SQL + 'FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID WHERE ' IF @timeID = 1 /* Return last 10 days */ SET @SQL = @SQL + ' Orders.OrderDate IN (SELECT DISTINCT TOP 10 Orders.OrderDate FROM Orders WHERE Orders.OrderDate <= ''' + convert(char(8), @dtMaxDate, 112) + '''' IF @timeID = 2 /* should Return records between start and end date */ PRINT 'BELOW CODE GIVES ERROR' SET @SQL = @SQL + CONVERT (char(8), Orders.OrderDate, 112) >= @dtStartDate + ' AND ' + CONVERT (char(8), Orders.OrderDate, 112) <= @dtEndDate SET @SQL = @SQL + ' ORDER BY Orders.OrderDate DESC) ' SET @SQL = @SQL + 'GROUP BY Orders.OrderDate' EXEC(@SQL) Basically, I think your dynamic SQL should look like this when done:
===== 'SELECT Orders.OrderDate, SUM(Orders.Freight) AS SumOfFreight FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID WHERE CONVERT (char(8), Orders.OrderDate, 112) >= ''' + CONVERT (CHAR(11), @dtStartDate) + ''' AND CONVERT (char(8), Orders.OrderDate, 112) <= ''' + CONVERT(CHAR(11), @dtEndDate) + '''' ===== From your example below, it looks like the CONVERT portion needs to be within the quotes and the @tdStartDate and @tdEndDates need to be appended to the query with their values. Also, what date format are you expecting by converting into CHAR(8)? -- Show quoteHTH, SriSamp Email: sris***@gmail.com Blog: http://blogs.sqlxml.org/srinivassampath URL: http://www32.brinkster.com/srisamp "Scott" <sbai***@mileslumber.com> wrote in message news:e3P2oYJ8FHA.2192@TK2MSFTNGP14.phx.gbl... > My dynamic sql correctly returns the top 10 dates with records if @timeID > equals 1. I need the @timeID equals 2 part to return records between > @dtStartDate and @dtEndDate. (I just set the enddate equal to maxdate to > keep my example simple). > > In the below section where @timeID = 2, I'm getting a syntax error. Can > anyone help with the part below that tests IF @timeID = 2 ? I just need > some quote help with that part of the where clause. > > This runs on Northwind. > > CODE ******************************************* > > declare @SQL varchar(1000), @typeID int, @timeID int > declare @dtStartDate datetime, @dtEndDate datetime, @dtMaxDate datetime > set @typeID = 1 > SET @timeID = 1 > > set @dtMaxDate = (SELECT MAX(OrderDate) FROM Orders) > set @dtStartDate = (SELECT DATEADD(day, -20, @dtMaxDate)) > set @dtEndDate = @dtMaxDate > > SET @SQL = 'SELECT Orders.OrderDate, ' > > IF @typeID = 1 > SET @SQL = @SQL + 'SUM(Orders.Freight) AS SumOfFreight ' > IF @typeID = 2 > SET @SQL = @SQL + 'COUNT(*) AS SalesCount ' > SET @SQL = @SQL + 'FROM Customers INNER JOIN Orders ON > Customers.CustomerID = Orders.CustomerID > WHERE ' > > IF @timeID = 1 /* Return last 10 days */ > SET @SQL = @SQL + ' Orders.OrderDate IN (SELECT DISTINCT TOP 10 > Orders.OrderDate > FROM Orders WHERE Orders.OrderDate <= ''' + convert(char(8), > @dtMaxDate, 112) + '''' > > IF @timeID = 2 /* should Return records between start and end date */ > PRINT 'BELOW CODE GIVES ERROR' > SET @SQL = @SQL + CONVERT (char(8), Orders.OrderDate, 112) >= > @dtStartDate + ' AND ' > + CONVERT (char(8), Orders.OrderDate, 112) <= @dtEndDate > > SET @SQL = @SQL + ' ORDER BY Orders.OrderDate DESC) ' > > SET @SQL = @SQL + 'GROUP BY Orders.OrderDate' > > EXEC(@SQL) > > > Thanks for the efforts.
Show quote "SriSamp" <ssamp***@sct.co.in> wrote in message news:enDhqcL8FHA.1140@tk2msftngp13.phx.gbl... > Basically, I think your dynamic SQL should look like this when done: > ===== > 'SELECT Orders.OrderDate, SUM(Orders.Freight) AS SumOfFreight FROM > Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID > WHERE CONVERT (char(8), Orders.OrderDate, 112) >= ''' + CONVERT (CHAR(11), > @dtStartDate) + ''' AND CONVERT (char(8), Orders.OrderDate, 112) <= ''' + > CONVERT(CHAR(11), @dtEndDate) + '''' > ===== > > From your example below, it looks like the CONVERT portion needs to be > within the quotes and the @tdStartDate and @tdEndDates need to be appended > to the query with their values. Also, what date format are you expecting > by converting into CHAR(8)? > -- > HTH, > SriSamp > Email: sris***@gmail.com > Blog: http://blogs.sqlxml.org/srinivassampath > URL: http://www32.brinkster.com/srisamp > > "Scott" <sbai***@mileslumber.com> wrote in message > news:e3P2oYJ8FHA.2192@TK2MSFTNGP14.phx.gbl... >> My dynamic sql correctly returns the top 10 dates with records if @timeID >> equals 1. I need the @timeID equals 2 part to return records between >> @dtStartDate and @dtEndDate. (I just set the enddate equal to maxdate to >> keep my example simple). >> >> In the below section where @timeID = 2, I'm getting a syntax error. Can >> anyone help with the part below that tests IF @timeID = 2 ? I just need >> some quote help with that part of the where clause. >> >> This runs on Northwind. >> >> CODE ******************************************* >> >> declare @SQL varchar(1000), @typeID int, @timeID int >> declare @dtStartDate datetime, @dtEndDate datetime, @dtMaxDate datetime >> set @typeID = 1 >> SET @timeID = 1 >> >> set @dtMaxDate = (SELECT MAX(OrderDate) FROM Orders) >> set @dtStartDate = (SELECT DATEADD(day, -20, @dtMaxDate)) >> set @dtEndDate = @dtMaxDate >> >> SET @SQL = 'SELECT Orders.OrderDate, ' >> >> IF @typeID = 1 >> SET @SQL = @SQL + 'SUM(Orders.Freight) AS SumOfFreight ' >> IF @typeID = 2 >> SET @SQL = @SQL + 'COUNT(*) AS SalesCount ' >> SET @SQL = @SQL + 'FROM Customers INNER JOIN Orders ON >> Customers.CustomerID = Orders.CustomerID >> WHERE ' >> >> IF @timeID = 1 /* Return last 10 days */ >> SET @SQL = @SQL + ' Orders.OrderDate IN (SELECT DISTINCT TOP 10 >> Orders.OrderDate >> FROM Orders WHERE Orders.OrderDate <= ''' + convert(char(8), >> @dtMaxDate, 112) + '''' >> >> IF @timeID = 2 /* should Return records between start and end date */ >> PRINT 'BELOW CODE GIVES ERROR' >> SET @SQL = @SQL + CONVERT (char(8), Orders.OrderDate, 112) >= >> @dtStartDate + ' AND ' >> + CONVERT (char(8), Orders.OrderDate, 112) <= @dtEndDate >> >> SET @SQL = @SQL + ' ORDER BY Orders.OrderDate DESC) ' >> >> SET @SQL = @SQL + 'GROUP BY Orders.OrderDate' >> >> EXEC(@SQL) >> >> >> > > Scott
I don't understand why would want to use dynamic sql but this is a fixed code ---- PRINT 'BELOW CODE GIVES ERROR' SET @SQL = @SQL + 'CONVERT (char(8), Orders.OrderDate, 112) >=''' + convert(char(8),@dtStartDate ,112)+ ''' AND ' + 'CONVERT (char(8), Orders.OrderDate, 112) <=''' +convert(char(8),@dtEndDate,112)+'''' BTW if IF @timeID = 1, I got this error Line 28: Incorrect syntax near '>'. Show quote "Scott" <sbai***@mileslumber.com> wrote in message news:e3P2oYJ8FHA.2192@TK2MSFTNGP14.phx.gbl... > My dynamic sql correctly returns the top 10 dates with records if @timeID > equals 1. I need the @timeID equals 2 part to return records between > @dtStartDate and @dtEndDate. (I just set the enddate equal to maxdate to > keep my example simple). > > In the below section where @timeID = 2, I'm getting a syntax error. Can > anyone help with the part below that tests IF @timeID = 2 ? I just need > some quote help with that part of the where clause. > > This runs on Northwind. > > CODE ******************************************* > > declare @SQL varchar(1000), @typeID int, @timeID int > declare @dtStartDate datetime, @dtEndDate datetime, @dtMaxDate datetime > set @typeID = 1 > SET @timeID = 1 > > set @dtMaxDate = (SELECT MAX(OrderDate) FROM Orders) > set @dtStartDate = (SELECT DATEADD(day, -20, @dtMaxDate)) > set @dtEndDate = @dtMaxDate > > SET @SQL = 'SELECT Orders.OrderDate, ' > > IF @typeID = 1 > SET @SQL = @SQL + 'SUM(Orders.Freight) AS SumOfFreight ' > IF @typeID = 2 > SET @SQL = @SQL + 'COUNT(*) AS SalesCount ' > SET @SQL = @SQL + 'FROM Customers INNER JOIN Orders ON > Customers.CustomerID = Orders.CustomerID > WHERE ' > > IF @timeID = 1 /* Return last 10 days */ > SET @SQL = @SQL + ' Orders.OrderDate IN (SELECT DISTINCT TOP 10 > Orders.OrderDate > FROM Orders WHERE Orders.OrderDate <= ''' + convert(char(8), > @dtMaxDate, 112) + '''' > > IF @timeID = 2 /* should Return records between start and end date */ > PRINT 'BELOW CODE GIVES ERROR' > SET @SQL = @SQL + CONVERT (char(8), Orders.OrderDate, 112) >= > @dtStartDate + ' AND ' > + CONVERT (char(8), Orders.OrderDate, 112) <= @dtEndDate > > SET @SQL = @SQL + ' ORDER BY Orders.OrderDate DESC) ' > > SET @SQL = @SQL + 'GROUP BY Orders.OrderDate' > > EXEC(@SQL) > > > Scott (sbai***@mileslumber.com) writes:
> My dynamic sql correctly returns the top 10 dates with records if @timeID Weren't you the guy with time constraints? Why then are you wasting the> equals 1. I need the @timeID equals 2 part to return records between > @dtStartDate and @dtEndDate. (I just set the enddate equal to maxdate to > keep my example simple). time with building complete SQL strings, when using sp_executesql is so much easier? And why do you insist on not printing out the generated SQL? You could at least include the generated SQL in your posts, so that it's easier for people to spot the syntax errors. > IF @timeID = 2 /* should Return records between start and end date */ You have string concatanated with string, and then there is a comparison> PRINT 'BELOW CODE GIVES ERROR' > SET @SQL = @SQL + CONVERT (char(8), Orders.OrderDate, 112) >= > @dtStartDate + ' AND ' operator in the middle of all this. That will obviously give a syntax error. And avoid doing things like CONVERT (char(8), Orders.OrderDate, 112) in an WHERE clause. When you put a column in an expression, this prevents any index from that column from being used. Thus, this can have a serious impact on performance. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx 1. I'd really appreciate it if you would expand on your "avoid doing things
like CONVERT (char(8)), Orders.OrderDate, 112)". I always have to pass dates from ASP to my SPROCs as "11/24/2005". How or what syntax can I use on that date format to avoid a performance hit? 2. As far as sp_executesql, I wanted to use that method, but the syntax is more confusing that my method. If I good get some good simple examples, I'd go that way. 3. My reason for going the way I am is if I hard coded every sql statement with the matrix of variables I'm sending this SPROC, it would be 1,000 lines or more. Normally they don't get this out of hand, but this one is for a graph with lots of options. 4. I'll do better at printing the SQL from now on. Thanks for any pointers on my date performance issue #1 above. Show quote "Erland Sommarskog" <esq***@sommarskog.se> wrote in message news:Xns9718610C29F6DYazorman@127.0.0.1... > Scott (sbai***@mileslumber.com) writes: >> My dynamic sql correctly returns the top 10 dates with records if @timeID >> equals 1. I need the @timeID equals 2 part to return records between >> @dtStartDate and @dtEndDate. (I just set the enddate equal to maxdate to >> keep my example simple). > > Weren't you the guy with time constraints? Why then are you wasting the > time with building complete SQL strings, when using sp_executesql is so > much easier? > > And why do you insist on not printing out the generated SQL? You could > at least include the generated SQL in your posts, so that it's easier for > people to spot the syntax errors. > >> IF @timeID = 2 /* should Return records between start and end date */ >> PRINT 'BELOW CODE GIVES ERROR' >> SET @SQL = @SQL + CONVERT (char(8), Orders.OrderDate, 112) >= >> @dtStartDate + ' AND ' > > You have string concatanated with string, and then there is a comparison > operator in the middle of all this. That will obviously give a syntax > error. > > And avoid doing things like CONVERT (char(8), Orders.OrderDate, 112) in > an WHERE clause. When you put a column in an expression, this prevents > any index from that column from being used. Thus, this can have a serious > impact on performance. > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Scott (sbai***@mileslumber.com) writes:
> 1. I'd really appreciate it if you would expand on your "avoid doing No, you don't have that pass dates that way from ASP. Even more you > things like CONVERT (char(8)), Orders.OrderDate, 112)". I always have to > pass dates from ASP to my SPROCs as "11/24/2005". How or what syntax can > I use on that date format to avoid a performance hit? shouldn't. That is not even a date as far as I am concerned. The way to pass data from a client is to use parameters, and let the the client interpret the date according to the regional setting. The is then passed to SQL Server as a binary value, as dates are binary values in SQL Server. (As they are in Windows, by the way.) I don't do ASP, but in plain Visual Basic it looks something like: cmd.CommandType = adCmdStoredProcedure cmd.CommandText = "dbo.some_sp" cmd.CreateParameter "@param1", adDBTimeStamp, adParamInput, , DateFld And obviously you are doing something like that already, as the date variables in your example were declared as datetime, not as character. > 2. As far as sp_executesql, I wanted to use that method, but the syntax SELECT @sql = N'SELECT ... FROM tbl WHERE col = @par1, col = @par2',> is more confusing that my method. If I good get some good simple > examples, I'd go that way. @params = N'@par1 int, @par2 datetime' EXEC sp_executesql @sql, @params, @par1 = 12, @par2 = @dtEndTime How is this more confusing that trying to get quotes and date formats correct, something you appear to have big problems with. As for writing dynamic searches, have you looked at my web site where I have a longer article on the topic, with both static and dynamic methods? http://www.sommarskog.se/dyn-search.html. There are also tips for people that use EXEC(). -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx The part of sp_executesql that gets confusing is the N' syntax. Does N' mean
it's a text type? Show quote "Erland Sommarskog" <esq***@sommarskog.se> wrote in message news:Xns9718E396131E5Yazorman@127.0.0.1... > Scott (sbai***@mileslumber.com) writes: >> 1. I'd really appreciate it if you would expand on your "avoid doing >> things like CONVERT (char(8)), Orders.OrderDate, 112)". I always have to >> pass dates from ASP to my SPROCs as "11/24/2005". How or what syntax can >> I use on that date format to avoid a performance hit? > > No, you don't have that pass dates that way from ASP. Even more you > shouldn't. That is not even a date as far as I am concerned. > > The way to pass data from a client is to use parameters, and let the > the client interpret the date according to the regional setting. The is > then passed to SQL Server as a binary value, as dates are binary values > in SQL Server. (As they are in Windows, by the way.) > > I don't do ASP, but in plain Visual Basic it looks something like: > > cmd.CommandType = adCmdStoredProcedure > cmd.CommandText = "dbo.some_sp" > cmd.CreateParameter "@param1", adDBTimeStamp, adParamInput, , DateFld > > And obviously you are doing something like that already, as the date > variables in your example were declared as datetime, not as character. > >> 2. As far as sp_executesql, I wanted to use that method, but the syntax >> is more confusing that my method. If I good get some good simple >> examples, I'd go that way. > > SELECT @sql = N'SELECT ... FROM tbl WHERE col = @par1, col = @par2', > @params = N'@par1 int, @par2 datetime' > EXEC sp_executesql @sql, @params, @par1 = 12, @par2 = @dtEndTime > > How is this more confusing that trying to get quotes and date formats > correct, something you appear to have big problems with. > > As for writing dynamic searches, have you looked at my web site where > I have a longer article on the topic, with both static and dynamic > methods? > http://www.sommarskog.se/dyn-search.html. There are also tips for people > that use EXEC(). > > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > The part of sp_executesql that gets confusing is the N' syntax. Does N' mean it's a text type? It mean it is Unicode.-- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "Scott" <sbai***@mileslumber.com> wrote in message news:ut24UCY8FHA.3952@TK2MSFTNGP12.phx.gbl... > The part of sp_executesql that gets confusing is the N' syntax. Does N' mean it's a text type? > > "Erland Sommarskog" <esq***@sommarskog.se> wrote in message > news:Xns9718E396131E5Yazorman@127.0.0.1... >> Scott (sbai***@mileslumber.com) writes: >>> 1. I'd really appreciate it if you would expand on your "avoid doing >>> things like CONVERT (char(8)), Orders.OrderDate, 112)". I always have to >>> pass dates from ASP to my SPROCs as "11/24/2005". How or what syntax can >>> I use on that date format to avoid a performance hit? >> >> No, you don't have that pass dates that way from ASP. Even more you >> shouldn't. That is not even a date as far as I am concerned. >> >> The way to pass data from a client is to use parameters, and let the >> the client interpret the date according to the regional setting. The is >> then passed to SQL Server as a binary value, as dates are binary values >> in SQL Server. (As they are in Windows, by the way.) >> >> I don't do ASP, but in plain Visual Basic it looks something like: >> >> cmd.CommandType = adCmdStoredProcedure >> cmd.CommandText = "dbo.some_sp" >> cmd.CreateParameter "@param1", adDBTimeStamp, adParamInput, , DateFld >> >> And obviously you are doing something like that already, as the date >> variables in your example were declared as datetime, not as character. >> >>> 2. As far as sp_executesql, I wanted to use that method, but the syntax >>> is more confusing that my method. If I good get some good simple >>> examples, I'd go that way. >> >> SELECT @sql = N'SELECT ... FROM tbl WHERE col = @par1, col = @par2', >> @params = N'@par1 int, @par2 datetime' >> EXEC sp_executesql @sql, @params, @par1 = 12, @par2 = @dtEndTime >> >> How is this more confusing that trying to get quotes and date formats >> correct, something you appear to have big problems with. >> >> As for writing dynamic searches, have you looked at my web site where >> I have a longer article on the topic, with both static and dynamic methods? >> http://www.sommarskog.se/dyn-search.html. There are also tips for people >> that use EXEC(). >> >> >> -- >> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se >> >> Books Online for SQL Server 2005 at >> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx >> Books Online for SQL Server 2000 at >> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > > on your site in search_orders_1, for example, how does SQL know what
@xorderid is when you only declare @orderid at the beginning of the SPROC? I see @xorderid listed in the @paramlist after you use it as a WHERE criteria, but how does SQL know what @xorderid is? Show quote "Erland Sommarskog" <esq***@sommarskog.se> wrote in message news:Xns9718E396131E5Yazorman@127.0.0.1... > Scott (sbai***@mileslumber.com) writes: >> 1. I'd really appreciate it if you would expand on your "avoid doing >> things like CONVERT (char(8)), Orders.OrderDate, 112)". I always have to >> pass dates from ASP to my SPROCs as "11/24/2005". How or what syntax can >> I use on that date format to avoid a performance hit? > > No, you don't have that pass dates that way from ASP. Even more you > shouldn't. That is not even a date as far as I am concerned. > > The way to pass data from a client is to use parameters, and let the > the client interpret the date according to the regional setting. The is > then passed to SQL Server as a binary value, as dates are binary values > in SQL Server. (As they are in Windows, by the way.) > > I don't do ASP, but in plain Visual Basic it looks something like: > > cmd.CommandType = adCmdStoredProcedure > cmd.CommandText = "dbo.some_sp" > cmd.CreateParameter "@param1", adDBTimeStamp, adParamInput, , DateFld > > And obviously you are doing something like that already, as the date > variables in your example were declared as datetime, not as character. > >> 2. As far as sp_executesql, I wanted to use that method, but the syntax >> is more confusing that my method. If I good get some good simple >> examples, I'd go that way. > > SELECT @sql = N'SELECT ... FROM tbl WHERE col = @par1, col = @par2', > @params = N'@par1 int, @par2 datetime' > EXEC sp_executesql @sql, @params, @par1 = 12, @par2 = @dtEndTime > > How is this more confusing that trying to get quotes and date formats > correct, something you appear to have big problems with. > > As for writing dynamic searches, have you looked at my web site where > I have a longer article on the topic, with both static and dynamic > methods? > http://www.sommarskog.se/dyn-search.html. There are also tips for people > that use EXEC(). > > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Scott (sbai***@mileslumber.com) writes:
>The part of sp_executesql that gets confusing is the N' syntax. Does N' No, it means that this is a Unicode literal, that is the type nvarchar.>mean it's a text type? It is not always necessary to specify N', but sometimes it is. For instance if you say: sp_executesql 'SELECT 12' you get: Server: Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1 Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'. Which is rectified by adding an N in front. When you are working with characters that are not in your 8-bit code page, it can also make a difference. By the way, this sort of notation is not peculiar to SQL. In C++ wide string literals are prepended by L. > on your site in search_orders_1, for example, how does SQL know what Because I declare my parameterlist as:> @xorderid is when you only declare @orderid at the beginning of the > SPROC? I see @xorderid listed in the @paramlist after you use it as a > WHERE criteria, but how does SQL know what @xorderid is? SELECT @paramlist = '@xorderid int, @xfromdate datetime, ... @xprodname nvarchar(40)' And then I pass this parameter list to sp_executesql as the second parameter: EXEC sp_executesql @sql, @paramlist, @orderid, @fromdate, @todate, @minprice, @maxprice, @custid, @custname, @city, @region, @country, @prodid, @prodname Normally, you would of course name the parameters @orderid, @fromdate etc, but I used @xorderid etc in the article to stress that the variables in the dynamic SQL are not the variables of the surrounding procedures. A chunk of dynamic SQL constitutes its own scope. In fact, you can view this as that you build stored procedure on the fly and then create it and execute it, in the same go. So conceptually, you could translate sp_executesql to: EXEC ('CREATE PROCEDURE #my_temp_proc ' + @paramlist + ' AS ' + @sql') EXEC #my_temp_proc @orderid, @fromdate, @todate, @minprice, ... -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx I understand now, but have 1 more question about dynamic sql. Given a SPROC
like your example, there is only 1 execution of 1 dynamic sql statement. What if I needed a 2nd execution within a dynamic SPROC? For example, normally I'd use syntax like SET @myvar = (SELECT MAX(date_field) from mytable where id = @somevariable) to get @myvar and use it in my dynamic code. What if my above example needed to be dynamic also. Is there a way to fire 2 EXECs within a SPROC? Show quote "Erland Sommarskog" <esq***@sommarskog.se> wrote in message news:Xns9719617018F83Yazorman@127.0.0.1... > Scott (sbai***@mileslumber.com) writes: >>The part of sp_executesql that gets confusing is the N' syntax. Does N' >>mean it's a text type? > > No, it means that this is a Unicode literal, that is the type nvarchar. > It is not always necessary to specify N', but sometimes it is. For > instance > if you say: > > sp_executesql 'SELECT 12' > > you get: > > Server: Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1 > Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'. > > Which is rectified by adding an N in front. When you are working with > characters that are not in your 8-bit code page, it can also make a > difference. > > By the way, this sort of notation is not peculiar to SQL. In C++ wide > string literals are prepended by L. > >> on your site in search_orders_1, for example, how does SQL know what >> @xorderid is when you only declare @orderid at the beginning of the >> SPROC? I see @xorderid listed in the @paramlist after you use it as a >> WHERE criteria, but how does SQL know what @xorderid is? > > Because I declare my parameterlist as: > > SELECT @paramlist = '@xorderid int, > @xfromdate datetime, > ... > @xprodname nvarchar(40)' > > And then I pass this parameter list to sp_executesql as the second > parameter: > > EXEC sp_executesql @sql, @paramlist, > @orderid, @fromdate, @todate, @minprice, @maxprice, > @custid, @custname, @city, @region, @country, > @prodid, @prodname > > Normally, you would of course name the parameters @orderid, @fromdate etc, > but I used @xorderid etc in the article to stress that the variables in > the dynamic SQL are not the variables of the surrounding procedures. > > A chunk of dynamic SQL constitutes its own scope. In fact, you can view > this as that you build stored procedure on the fly and then create it > and execute it, in the same go. So conceptually, you could translate > sp_executesql to: > > EXEC ('CREATE PROCEDURE #my_temp_proc ' + @paramlist + ' AS ' + @sql') > EXEC #my_temp_proc @orderid, @fromdate, @todate, @minprice, ... > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Scott (sbai***@mileslumber.com) writes:
> I understand now, but have 1 more question about dynamic sql. Given a I'm not really sure what the question is. It is of course perfectly > SPROC like your example, there is only 1 execution of 1 dynamic sql > statement. What if I needed a 2nd execution within a dynamic SPROC? possible to call sp_executesql more than once with the same SQL statement, with the same or different values for the parameters. The nice thing here, is that SQL Server will reuse the query plan from the first invocation, so the query will not be compiled the second time, thereby saving some cycles. > For example, normally I'd use syntax like SET @myvar = (SELECT But this clarification makes me think that your question is not the one> MAX(date_field) from mytable where id = @somevariable) to get @myvar and > use it in my dynamic code. What if my above example needed to be dynamic > also. Is there a way to fire 2 EXECs within a SPROC? I just answered. If the question is whether you can get data out of the dynamic SQL statement, the answer is yes. As I said, this is just like a stored procedure created on the fly, so in the parameter list you can use the OUTPUT keyword: @sql = 'SELECT @myvar = MAX(date) FROM ' + @thattable @params = '@myvar datetime OUTPUT' EXEC sp_executesql @sql, @params, @datevalue OUTPUT -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx  |
|||||||||||||||||||||||
Other interesting topics