I have a sproc that runs as a job every day. Since the first of the year, it hasn't been running properly (it errors out). It builds an SQL statement dynamically, and then executes it.
If I try to run it with QA, I get the following message:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'FCST'.
UPDATE OPSPLAN SET Jan_07=Jan_fcst, Feb_07=Feb_fcst, ...
However, if I just copy the entire Update statement contained in the error message into the QA window, and execute it, it runs just fine.
What could I be missing?
UPDATE OPSPLAN SET Jan_07=Jan_fcst, Feb_07=Feb_fcst, Mar_07=Mar_fcst,
Apr_07=Apr_fcst, May_07=May_fcst, Jun_07=Jun_fcst, Jul_07=Jul_fcst,
Aug_07=Aug_fcst, Sep_07=Sep_fcst, Oct_07=Oct_fcst, Nov_07=Nov_fcst,
Dec_07=Dec_fcst
FROM (SELECT [YEAR], PLAN_SHIP.BOD_INDEX, BOD_HEADER.PRODUCT,
Jan_fcst, Feb_fcst, Mar_fcst, Apr_fcst, May_fcst, Jun_fcst, Jul_fcst,
Aug_fcst, Sep_fcst, Oct_fcst, Nov_fcst, Dec_fcst
FROM PLAN_SHIP INNER JOIN BOD_HEADER
ON PLAN_SHIP.BOD_INDEX = BOD_HEADER.BOD_INDEX
WHERE (SCEN_ID = 1) AND ([Year] = 2007)
) PS INNER JOIN OPSPLAN ON PS.BOD_INDEX = OPSPLAN.BOD_INDEX
WHERE OPSPLAN.SRCPLAN = 'SHIP'I'd sic the SQL Profiler on this fella. My suspicion is that the UPDATE is being mis-parsed, possibly because of a syntax error within the previous SQL statement. Profiler ought to give you some clues if that is the case. No outright answers, just clues, but that's more than you have now.
-PatP|||OK, I read up on it in BOL, and figured out how to get profiler running.
I found the line where that particular statement is executing.
I'm looking at StmtStarting and StmtCompleted. Is there something in
particular that I should be looking for?|||Since the string "FCST" does not appear independently in your code, but only in conjuction with a month and an underscore character, I'd say that somewhere and underscore character is being dropped.
Set the dynamic sql statement to print rather than execute, and bump up the Max characters setting in the Query Analyzer Options. Then see what code is actually being executed.|||Yeah, that "FCST" was throwing me, too. All of the instances in my string
are "fcst" not "FCST". Anyway, after pouring through the sproc over and over, I found a PRINT statement that was causing the completely valild statement to display under the error message. It wasn't the cause at all,
it was a previous statement. After I eliminated that, it was easy to narrow it down.
Thanks
Showing posts with label builds. Show all posts
Showing posts with label builds. Show all posts
Tuesday, March 27, 2012
Monday, March 19, 2012
Dynamic IN clause
I have a "can this be done" question...
I currently have a stored procedure that builds a string into a SQL
Statement and runs it.
This is an abreviated version as an example:
CREATE PROCEDURE stp_ClaimInfo
@.DFTAX# VARCHAR(12),
@.TCLAIM varchar(1000)
AS
BEGIN
DECLARE @.SQLstr varchar(4000)
set @.SQLstr = ' SELECT TMBR#, TCLAIM, SUM(TFLD16) AS TTFLD16 '
+ ' FROM tbl_claim_status'
+ ' WHERE DFTAX# = ''' + @.DFTAX# + ''''
+ ' AND TCLAIM IN (' + @.TCLAIM + ')'
+ ' GROUP BY TMBR#, TCLAIM'
exec(@.SQLstr)
END
And the run it as:
EXEC stp_ClaimInfo '86- 0291651','606900122,606900121,606800078'
The reason is because of the "IN" cluase. I need to pass it claim numbers,
but there can be one or more at a time, and different each time it is run.
In effect, the above query ends up as:
SELECT TMBR#, TCLAIM, SUM(TFLD16) AS TTFLD16
FROM tbl_claim_status
WHERE DFTAX# = '86-0291651'
AND TCLAIM IN (606900122,606900121,606800078)
GROUP BY TMBR#, TCLAIM
My question is how can I pass a dynamic list of values for the IN clause but
not have to write the statement as a string then execute it using
"exec(@.SQLstr)"? Can this be done?
-- AndrewUse the sp_executesql , this way you can add parameter definition. This
is an example i used in an SP with OUTPUT parameters.
---
USE OUTPUT PARAM
http://support.microsoft.com/defaul...B;EN-US;q262499
---
DECLARE @.v_sql = NVARCHAR(4000)
SET @.v_sql = N'SELECT @.v_totalrowcountOUT = isnull(count(1),0) FROM
'+@.v_tablename_vc
SET @.ParmDefinition = N'@.v_totalrowcountOUT INT OUTPUT '
EXECUTE sp_executesql @.v_sql , @.ParmDefinition , @.v_totalrowcountOUT =@.
v_totalrowcount OUTPUT
---
USE INPUT PARAM
---
/* Build the SQL string once.*/
DECLARE @.SQLString NVARCHAR(4000)
DECLARE @.IntVariable INT
SET @.IntVariable = 100
SET @.SQLString = N'SELECT * FROM pubs.dbo.employee WHERE job_lvl =
@.level'
SET @.ParmDefinition = N'@.level tinyint'
/* Execute the string with the first parameter value. */
SET @.IntVariable = 35
EXECUTE sp_executesql @.SQLString, @.ParmDefinition, @.level = @.IntVariable|||> My question is how can I pass a dynamic list of values for the IN clause
> but not have to write the statement as a string then execute it using
> "exec(@.SQLstr)"? Can this be done?
SQL Server does not know what an array is, so no, you can't fool it into
believing this string is an array of ints. You can, however, encapsulate
splitting functionality off into another object.
http://www.aspfaq.com/2248
A|||You could do something like
AND ',' + @.TCLAIM + ',' LIKE '%,' + TCLAIM + ',%'
its not pretty though
"Andrew" wrote:
> I have a "can this be done" question...
> I currently have a stored procedure that builds a string into a SQL
> Statement and runs it.
> This is an abreviated version as an example:
> CREATE PROCEDURE stp_ClaimInfo
> @.DFTAX# VARCHAR(12),
> @.TCLAIM varchar(1000)
> AS
> BEGIN
> DECLARE @.SQLstr varchar(4000)
> set @.SQLstr = ' SELECT TMBR#, TCLAIM, SUM(TFLD16) AS TTFLD16 '
> + ' FROM tbl_claim_status'
> + ' WHERE DFTAX# = ''' + @.DFTAX# + ''''
> + ' AND TCLAIM IN (' + @.TCLAIM + ')'
> + ' GROUP BY TMBR#, TCLAIM'
> exec(@.SQLstr)
> END
> And the run it as:
> EXEC stp_ClaimInfo '86- 0291651','606900122,606900121,606800078'
> The reason is because of the "IN" cluase. I need to pass it claim numbers
,
> but there can be one or more at a time, and different each time it is run.
> In effect, the above query ends up as:
> SELECT TMBR#, TCLAIM, SUM(TFLD16) AS TTFLD16
> FROM tbl_claim_status
> WHERE DFTAX# = '86-0291651'
> AND TCLAIM IN (606900122,606900121,606800078)
> GROUP BY TMBR#, TCLAIM
>
> My question is how can I pass a dynamic list of values for the IN clause b
ut
> not have to write the statement as a string then execute it using
> "exec(@.SQLstr)"? Can this be done?
> -- Andrew
>
>|||Andrew (AndrewR2k1@.hotmail.com) writes:
> My question is how can I pass a dynamic list of values for the IN clause
> but not have to write the statement as a string then execute it using
> "exec(@.SQLstr)"? Can this be done?
Indeed, this can be done in a multitude of ways. For a quick start,
look at http://www.sommarskog.se/arrays-in-...ist-of-strings.
To see more ways, read the rest of the article.
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|||dizzler (john.dacosta@.gmail.com) writes:
> Use the sp_executesql , this way you can add parameter definition. This
> is an example i used in an SP with OUTPUT parameters.
sp_executesql does not change matters here. You would still have
to interpolate the list of values into the SQL string, you cannot
pass it as a parameter to sp_executesql.
The correct answer is that this is a task that you should not use
dynamic SQL at all for.
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|||You do not understand SQL at all and want to keep writing BASIC. And
you use tbl- prfixes, # in names and what look like dat type prefixes
on column names. And put it in uppercase to make it harder to read. I
also loved seeing "fld_16" vague and it lets us know that you still
thinkin fields nad do not know about relational columns. You cannot
have a table named "_status" because a status is an atrribute not an
entity.
CREATE PROCEDURE GetClaimsList
(@.my_tax_nbr CHAR(10), @.p01 CHAR(9), @.p02 CHAR(9), .., @.p50 CHAR(9))
AS
SELECT member_nbr, claim_nbr SUM(fld_6) AS foobar_total
FROM Claims
WHERE df_tax_nbr = @.my_tax_nbr
AND claim_nbr IN (@.p01 CHAR(9), @.p02 CHAR(9), .. , @.p50 CHAR(9))
GROUP BY member_nbr, claim_nbr ;
Did you know that a T-SQL proc can have over 1000 parameters? Do you
eer need more than that? Wow! Compiled code that ports easily.|||dizzler,
Thank you for the reply, but I feel you missed the point of my question.
-- Andrew
"dizzler" <john.dacosta@.gmail.com> wrote in message
news:1143668337.316099.27080@.i40g2000cwc.googlegroups.com...
> Use the sp_executesql , this way you can add parameter definition. This
> is an example i used in an SP with OUTPUT parameters.
> ---
> USE OUTPUT PARAM
> http://support.microsoft.com/defaul...B;EN-US;q262499
> ---
> DECLARE @.v_sql = NVARCHAR(4000)
> SET @.v_sql = N'SELECT @.v_totalrowcountOUT = isnull(count(1),0) FROM
> '+@.v_tablename_vc
> SET @.ParmDefinition = N'@.v_totalrowcountOUT INT OUTPUT '
> EXECUTE sp_executesql @.v_sql , @.ParmDefinition , @.v_totalrowcountOUT =@.
> v_totalrowcount OUTPUT
>
> ---
> USE INPUT PARAM
> ---
> /* Build the SQL string once.*/
> DECLARE @.SQLString NVARCHAR(4000)
> DECLARE @.IntVariable INT
> SET @.IntVariable = 100
> SET @.SQLString = N'SELECT * FROM pubs.dbo.employee WHERE job_lvl =
> @.level'
> SET @.ParmDefinition = N'@.level tinyint'
> /* Execute the string with the first parameter value. */
> SET @.IntVariable = 35
> EXECUTE sp_executesql @.SQLString, @.ParmDefinition, @.level = @.IntVariable
>|||Phillip,
Thank you for the reply, but your suggestion just wouldn't work for the
situation I have as the incoming string is a comma deliminated list and
trying to break it apart outside the SQL Statement would reduce the
efficiency of the whole process. In a different situation, your suggestion
could prove to be a useful answer.
-- Andrew
"Phillip Wilson" <Phillip Wilson@.discussions.microsoft.com> wrote in message
news:12B92F8B-101B-4184-8351-365B1D786293@.microsoft.com...
> You could do something like
> AND ',' + @.TCLAIM + ',' LIKE '%,' + TCLAIM + ',%'
> its not pretty though
> "Andrew" wrote:
>|||Aaron,
Thank you for the reply, this is the answer I was looking for...fantastic
ideas here. Thank ya much!
-- Andrew
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uGW%23Zo3UGHA.1688@.TK2MSFTNGP11.phx.gbl...
> SQL Server does not know what an array is, so no, you can't fool it into
> believing this string is an array of ints. You can, however, encapsulate
> splitting functionality off into another object.
> http://www.aspfaq.com/2248
> A
>
I currently have a stored procedure that builds a string into a SQL
Statement and runs it.
This is an abreviated version as an example:
CREATE PROCEDURE stp_ClaimInfo
@.DFTAX# VARCHAR(12),
@.TCLAIM varchar(1000)
AS
BEGIN
DECLARE @.SQLstr varchar(4000)
set @.SQLstr = ' SELECT TMBR#, TCLAIM, SUM(TFLD16) AS TTFLD16 '
+ ' FROM tbl_claim_status'
+ ' WHERE DFTAX# = ''' + @.DFTAX# + ''''
+ ' AND TCLAIM IN (' + @.TCLAIM + ')'
+ ' GROUP BY TMBR#, TCLAIM'
exec(@.SQLstr)
END
And the run it as:
EXEC stp_ClaimInfo '86- 0291651','606900122,606900121,606800078'
The reason is because of the "IN" cluase. I need to pass it claim numbers,
but there can be one or more at a time, and different each time it is run.
In effect, the above query ends up as:
SELECT TMBR#, TCLAIM, SUM(TFLD16) AS TTFLD16
FROM tbl_claim_status
WHERE DFTAX# = '86-0291651'
AND TCLAIM IN (606900122,606900121,606800078)
GROUP BY TMBR#, TCLAIM
My question is how can I pass a dynamic list of values for the IN clause but
not have to write the statement as a string then execute it using
"exec(@.SQLstr)"? Can this be done?
-- AndrewUse the sp_executesql , this way you can add parameter definition. This
is an example i used in an SP with OUTPUT parameters.
---
USE OUTPUT PARAM
http://support.microsoft.com/defaul...B;EN-US;q262499
---
DECLARE @.v_sql = NVARCHAR(4000)
SET @.v_sql = N'SELECT @.v_totalrowcountOUT = isnull(count(1),0) FROM
'+@.v_tablename_vc
SET @.ParmDefinition = N'@.v_totalrowcountOUT INT OUTPUT '
EXECUTE sp_executesql @.v_sql , @.ParmDefinition , @.v_totalrowcountOUT =@.
v_totalrowcount OUTPUT
---
USE INPUT PARAM
---
/* Build the SQL string once.*/
DECLARE @.SQLString NVARCHAR(4000)
DECLARE @.IntVariable INT
SET @.IntVariable = 100
SET @.SQLString = N'SELECT * FROM pubs.dbo.employee WHERE job_lvl =
@.level'
SET @.ParmDefinition = N'@.level tinyint'
/* Execute the string with the first parameter value. */
SET @.IntVariable = 35
EXECUTE sp_executesql @.SQLString, @.ParmDefinition, @.level = @.IntVariable|||> My question is how can I pass a dynamic list of values for the IN clause
> but not have to write the statement as a string then execute it using
> "exec(@.SQLstr)"? Can this be done?
SQL Server does not know what an array is, so no, you can't fool it into
believing this string is an array of ints. You can, however, encapsulate
splitting functionality off into another object.
http://www.aspfaq.com/2248
A|||You could do something like
AND ',' + @.TCLAIM + ',' LIKE '%,' + TCLAIM + ',%'
its not pretty though
"Andrew" wrote:
> I have a "can this be done" question...
> I currently have a stored procedure that builds a string into a SQL
> Statement and runs it.
> This is an abreviated version as an example:
> CREATE PROCEDURE stp_ClaimInfo
> @.DFTAX# VARCHAR(12),
> @.TCLAIM varchar(1000)
> AS
> BEGIN
> DECLARE @.SQLstr varchar(4000)
> set @.SQLstr = ' SELECT TMBR#, TCLAIM, SUM(TFLD16) AS TTFLD16 '
> + ' FROM tbl_claim_status'
> + ' WHERE DFTAX# = ''' + @.DFTAX# + ''''
> + ' AND TCLAIM IN (' + @.TCLAIM + ')'
> + ' GROUP BY TMBR#, TCLAIM'
> exec(@.SQLstr)
> END
> And the run it as:
> EXEC stp_ClaimInfo '86- 0291651','606900122,606900121,606800078'
> The reason is because of the "IN" cluase. I need to pass it claim numbers
,
> but there can be one or more at a time, and different each time it is run.
> In effect, the above query ends up as:
> SELECT TMBR#, TCLAIM, SUM(TFLD16) AS TTFLD16
> FROM tbl_claim_status
> WHERE DFTAX# = '86-0291651'
> AND TCLAIM IN (606900122,606900121,606800078)
> GROUP BY TMBR#, TCLAIM
>
> My question is how can I pass a dynamic list of values for the IN clause b
ut
> not have to write the statement as a string then execute it using
> "exec(@.SQLstr)"? Can this be done?
> -- Andrew
>
>|||Andrew (AndrewR2k1@.hotmail.com) writes:
> My question is how can I pass a dynamic list of values for the IN clause
> but not have to write the statement as a string then execute it using
> "exec(@.SQLstr)"? Can this be done?
Indeed, this can be done in a multitude of ways. For a quick start,
look at http://www.sommarskog.se/arrays-in-...ist-of-strings.
To see more ways, read the rest of the article.
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|||dizzler (john.dacosta@.gmail.com) writes:
> Use the sp_executesql , this way you can add parameter definition. This
> is an example i used in an SP with OUTPUT parameters.
sp_executesql does not change matters here. You would still have
to interpolate the list of values into the SQL string, you cannot
pass it as a parameter to sp_executesql.
The correct answer is that this is a task that you should not use
dynamic SQL at all for.
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|||You do not understand SQL at all and want to keep writing BASIC. And
you use tbl- prfixes, # in names and what look like dat type prefixes
on column names. And put it in uppercase to make it harder to read. I
also loved seeing "fld_16" vague and it lets us know that you still
thinkin fields nad do not know about relational columns. You cannot
have a table named "_status" because a status is an atrribute not an
entity.
CREATE PROCEDURE GetClaimsList
(@.my_tax_nbr CHAR(10), @.p01 CHAR(9), @.p02 CHAR(9), .., @.p50 CHAR(9))
AS
SELECT member_nbr, claim_nbr SUM(fld_6) AS foobar_total
FROM Claims
WHERE df_tax_nbr = @.my_tax_nbr
AND claim_nbr IN (@.p01 CHAR(9), @.p02 CHAR(9), .. , @.p50 CHAR(9))
GROUP BY member_nbr, claim_nbr ;
Did you know that a T-SQL proc can have over 1000 parameters? Do you
eer need more than that? Wow! Compiled code that ports easily.|||dizzler,
Thank you for the reply, but I feel you missed the point of my question.
-- Andrew
"dizzler" <john.dacosta@.gmail.com> wrote in message
news:1143668337.316099.27080@.i40g2000cwc.googlegroups.com...
> Use the sp_executesql , this way you can add parameter definition. This
> is an example i used in an SP with OUTPUT parameters.
> ---
> USE OUTPUT PARAM
> http://support.microsoft.com/defaul...B;EN-US;q262499
> ---
> DECLARE @.v_sql = NVARCHAR(4000)
> SET @.v_sql = N'SELECT @.v_totalrowcountOUT = isnull(count(1),0) FROM
> '+@.v_tablename_vc
> SET @.ParmDefinition = N'@.v_totalrowcountOUT INT OUTPUT '
> EXECUTE sp_executesql @.v_sql , @.ParmDefinition , @.v_totalrowcountOUT =@.
> v_totalrowcount OUTPUT
>
> ---
> USE INPUT PARAM
> ---
> /* Build the SQL string once.*/
> DECLARE @.SQLString NVARCHAR(4000)
> DECLARE @.IntVariable INT
> SET @.IntVariable = 100
> SET @.SQLString = N'SELECT * FROM pubs.dbo.employee WHERE job_lvl =
> @.level'
> SET @.ParmDefinition = N'@.level tinyint'
> /* Execute the string with the first parameter value. */
> SET @.IntVariable = 35
> EXECUTE sp_executesql @.SQLString, @.ParmDefinition, @.level = @.IntVariable
>|||Phillip,
Thank you for the reply, but your suggestion just wouldn't work for the
situation I have as the incoming string is a comma deliminated list and
trying to break it apart outside the SQL Statement would reduce the
efficiency of the whole process. In a different situation, your suggestion
could prove to be a useful answer.
-- Andrew
"Phillip Wilson" <Phillip Wilson@.discussions.microsoft.com> wrote in message
news:12B92F8B-101B-4184-8351-365B1D786293@.microsoft.com...
> You could do something like
> AND ',' + @.TCLAIM + ',' LIKE '%,' + TCLAIM + ',%'
> its not pretty though
> "Andrew" wrote:
>|||Aaron,
Thank you for the reply, this is the answer I was looking for...fantastic
ideas here. Thank ya much!
-- Andrew
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uGW%23Zo3UGHA.1688@.TK2MSFTNGP11.phx.gbl...
> SQL Server does not know what an array is, so no, you can't fool it into
> believing this string is an array of ints. You can, however, encapsulate
> splitting functionality off into another object.
> http://www.aspfaq.com/2248
> A
>
Subscribe to:
Posts (Atom)