April 19, 2010 at 5:33 am
Below is my stored procedure that creates a temp table then based on a different table it creates new columns.
In my 'Results' tab I get the expected results. In the 'Messages' tab I get the following:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'varchar'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'varchar'.
I am sure it's do to the sp_executesql lines. Is there a fix for this?
ALTER PROCEDURE [db].[sp_CustomersPartnersChart]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @PARTNER_CODE varchar(5)
DECLARE @PARTNER_NAME varchar(50)
DECLARE @PARTNER_COL1 varchar(60)
DECLARE @PARTNER_COL2 varchar(60)
DECLARE @SQL nvarchar(1000)
-- Insert statements for procedure here
CREATE TABLE #tblPartners
(
c_id int,
c_name varchar(50),
)
insert into #tblPartners (c_id, c_name) select c_id, c_name from customer
DECLARE my_cursor CURSOR FOR
SELECT p_code, p_name FROM partner WHERE p_active_cd = 1
OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @PARTNER_CODE, @PARTNER_NAME
WHILE @@FETCH_STATUS <> -1
BEGIN
SET @SQL = ''
SET @SQL = 'ALTER TABLE #tblPartners ADD ' + @PARTNER_CODE + '_GFF varchar(20)'
Exec sp_executesql @SQL
--PRINT @SQL
SET @SQL = 'ALTER TABLE #tblPartners ADD ' + @PARTNER_NAME + '_ANY varchar(20)'
Exec sp_executesql @SQL
--PRINT @SQL
FETCH NEXT FROM my_cursor INTO @PARTNER_CODE, @PARTNER_NAME
END
CLOSE my_cursor
DEALLOCATE my_cursor
SELECT * FROM #tblPartners
DROP table #tblPartners
END
April 19, 2010 at 5:41 am
1. After the first EXEC(@SQL), flush the @SQL variable; like , SET @SQL = ''
2. Check if the partnercode and partnername column values are not null and then use it up to populate the local variable mate...
I sense that the last iteration of the cursor is causing the problem.. so just check for the sanity of the column values before concatenating it with the local variables.
Tell us if this fixed the problem, llemon! 🙂
April 19, 2010 at 5:48 am
I tried the following and still get the same Message.
ALTER PROCEDURE [db].[sp_CustomersPartnersChart]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @PARTNER_CODE varchar(5)
DECLARE @PARTNER_NAME varchar(50)
DECLARE @PARTNER_COL1 varchar(60)
DECLARE @PARTNER_COL2 varchar(60)
DECLARE @SQL nvarchar(1000)
-- Insert statements for procedure here
CREATE TABLE #tblPartners
(
c_id int,
c_name varchar(50),
)
insert into #tblPartners (c_id, c_name) select c_id, c_name from customer
DECLARE my_cursor CURSOR FOR
SELECT p_code, p_name FROM partner WHERE p_active_cd = 1
OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @PARTNER_CODE, @PARTNER_NAME
WHILE @@FETCH_STATUS <> -1
IF @PARTNER_CODE <> '' AND @PARTNER_NAME <> ''
BEGIN
SET @SQL = ''
SET @SQL = 'ALTER TABLE #tblPartners ADD ' + @PARTNER_CODE + '_GFF varchar(20)'
EXEC sp_executesql @SQL
--PRINT @SQL
set @SQL = ''
SET @SQL = 'ALTER TABLE #tblPartners ADD ' + @PARTNER_NAME + '_ANY varchar(20)'
EXEC sp_executesql @SQL
--PRINT @SQL
set @SQL = ''
FETCH NEXT FROM my_cursor INTO @PARTNER_CODE, @PARTNER_NAME
END
CLOSE my_cursor
DEALLOCATE my_cursor
SELECT * FROM #tblPartners
DROP table #tblPartners
END
April 19, 2010 at 5:59 am
For the below statment,
IF @PARTNER_CODE <> '' AND @PARTNER_NAME <> ''
add ISNULL check also and try mate.. lets see if that help...
P.S: I am writing this from home where i dont have access to SSMS. That is why i am unable to give u exact issue and it's fix. My apologies!
April 19, 2010 at 6:39 am
Can you give it a try replacing this line: SET @SQL = 'ALTER TABLE #tblPartners ADD [' + @PARTNER_CODE + '_GFF] varchar(20)' ?
April 19, 2010 at 6:48 am
That seems to be the trick.
Here is the code with no errors. Should I still add the ISNULL check? I am not sure how you do that in the IF statement. I was only thinking you do ISNULL like so: ISNULL(value, replacingvalue)
Thanks to everyone for their quick responses.
ALTER PROCEDURE [db].[sp_CustomersPartnersChart]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @PARTNER_CODE varchar(5)
DECLARE @PARTNER_NAME varchar(50)
DECLARE @PARTNER_COL1 varchar(60)
DECLARE @PARTNER_COL2 varchar(60)
DECLARE @SQL nvarchar(1000)
-- Insert statements for procedure here
CREATE TABLE #tblPartners
(
c_id int,
c_name varchar(50),
)
insert into #tblPartners (c_id, c_name) select c_id, c_name from customer
DECLARE my_cursor CURSOR FOR
SELECT p_code, p_name FROM partner WHERE p_active_cd = 1
OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @PARTNER_CODE, @PARTNER_NAME
WHILE @@FETCH_STATUS <> -1
IF @PARTNER_CODE <> '' AND @PARTNER_NAME <> ''
BEGIN
SET @SQL = ''
SET @SQL = 'ALTER TABLE #tblPartners ADD [' + @PARTNER_CODE + '_GFF] varchar(20)'
EXEC sp_executesql @SQL
--PRINT @SQL
set @SQL = ''
SET @SQL = 'ALTER TABLE #tblPartners ADD [' + @PARTNER_NAME + '_ANY] varchar(20)'
EXEC sp_executesql @SQL
--PRINT @SQL
set @SQL = ''
FETCH NEXT FROM my_cursor INTO @PARTNER_CODE, @PARTNER_NAME
END
CLOSE my_cursor
DEALLOCATE my_cursor
SELECT * FROM #tblPartners
DROP table #tblPartners
END
April 19, 2010 at 6:50 am
Also i got a doubt here; Are there any values in the p_code, p_name columns that dont comply to the Rules of Identifiers?? [For rules of identifiers CLICK HERE - Rules of Identifiers ]
If any one value in the p_code, p_name columns are not a valid name for SQL Server, the cursor code will fail... Just check values in the #tblPartners if they comply with Rules of Identifiers!!
April 19, 2010 at 6:55 am
I'm surprised you're not getting errors, because you should
CREATE TABLE #tblPartners
(
c_id int,
c_name varchar(50),
)
That can't run due to the comma after the (50). It should look like this:
CREATE TABLE #tblPartners
(
c_id int,
c_name varchar(50)
)
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 19, 2010 at 7:45 am
lleemon13 (4/19/2010)
That seems to be the trick.Here is the code with no errors. Should I still add the ISNULL check? I am not sure how you do that in the IF statement. I was only thinking you do ISNULL like so: ISNULL(value, replacingvalue)
Include them if that wont hurt your query performance; it;s good to give machines smaller amount of data to process 😀
And for the error and your fix, i see that your values in the columns are having spaces between them.. hmmm.. good that your code fixed and is raring to go 🙂
Cheers!!
April 19, 2010 at 12:53 pm
Should I still add the ISNULL check? I am not sure how you do that in the IF statement. I was only thinking you do ISNULL like so: ISNULL(value, replacingvalue)
You could do it like this:
IF ISNULL(@PARTNER_CODE,'') <> '' AND ISNULL(@PARTNER_NAME,'') <> ''
Rob Schripsema
Propack, Inc.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply