March 11, 2011 at 3:38 pm
Hello, i have the following code in a stored procedure that is not working:
SET @sqlStmt1 = 'SELECT Cast(c.COLUMN_NAME As varchar(100)) + '' + '' + '''''',''''''
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
WHERE pk.TABLE_NAME =' + '''' + @i_tab_nm + '''' +
' AND CONSTRAINT_TYPE = ''PRIMARY KEY''
AND c.TABLE_NAME = pk.TABLE_NAME
AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME'
EXECUTE (@sqlStmt1)
-- Removes the unnecessary characters from the end.
SET @v_pkStr = SUBSTRING(@v_pkStr, 1, Len(@v_pkStr) - 6)
SELECT '@v_pkStr: ' + @v_pkStr
My question is how can I put the results of the EXECUTE into a variable for use later in my SP?
Best Regards,
~David
March 11, 2011 at 3:51 pm
I'm not sure you need the dynamic SQL there since your only variable is after the = sign in the query.
To answer your question, yes, you could use it later in the query if it ran without error.
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
March 11, 2011 at 3:52 pm
Hello,
From what you posted, the dynamic sql is not necessary. If the table name is passed into the stored procedure as a variable (say @table_name varchar(100)), then your query would look something like this:
Select @Col_Name=c.column_name from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk, INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
Where CONSTRAINT_TYPE='Primary Key' and c.TABLE_NAME=pk.TABLE_NAME
and c.CONSTRAINT_NAME=pk.constraint_name and pk.TABLE_NAME=@table_name
The result of the query will be held in the variable "@Col_name"
HTH
Elliott
March 11, 2011 at 3:54 pm
Also, you should rewrite the query to use an INNER JOIN rather than the older way its using more than one table.
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
March 11, 2011 at 7:42 pm
Okay, the sp is not working. Here is the entire sp:
The code in bold is not working. The @v_pkStr variable is always NULL.
ALTER PROCEDURE [esell].[SP_VERIFY_DFT_COLUMNS]
@i_tab_nm NVARCHAR(100),
@i_col_lst NVARCHAR(100),
@o_rtn_cd INT OUTPUT
AS
DECLARE
@Parameter NVARCHAR(4000),
@sqlStmt NVARCHAR(4000),
@sqlStmt1 NVARCHAR(4000),
@v_pkStr NVARCHAR(4000),
@cnt INT,
@tmpMessage NVARCHAR(2000),
@Counter INT,
@colNm NVARCHAR(25)
BEGIN TRY
--SELECT @i_tab_nm
--SELECT @i_col_lst
CREATE TABLE ##Tally
(N INT,
CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N)
)
SET @Counter = 1
WHILE @Counter <= 11000
BEGIN
INSERT INTO ##Tally
(N)
VALUES (@Counter)
SET @Counter = @Counter + 1
END
SELECT @v_pkStr = @v_pkStr + 'CAST(' + Cast(c.COLUMN_NAME As varchar(100)) + ' AS VARCHAR(100))' + '+' + ''',''' + '+'
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
WHERE pk.TABLE_NAME = @i_tab_nm
AND CONSTRAINT_TYPE = 'PRIMARY KEY'
AND c.TABLE_NAME = pk.TABLE_NAME
AND c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
-- Removes the unecessary characters from the end.
SET @v_pkStr = SUBSTRING(@v_pkStr, 1, Len(@v_pkStr) - 5)
--===== Create a table to store the results in
DECLARE @Elements TABLE
(
Number INT IDENTITY(1,1), --Order it appears in original string
Value VARCHAR(50)--The string value of the element
);
--===== Add start and end commas to the Parameter so we can handle -- single elements
SET @Parameter = ','+ @i_col_lst +','
INSERT INTO @Elements
(Value)
SELECT SUBSTRING(@Parameter,N+1,CHARINDEX(',',@Parameter,N+1)-N-1)
FROM ##Tally
WHERE N < LEN(@Parameter)
AND SUBSTRING(@Parameter,N,1) = ',' --Notice how we find the comma
SET @cnt = @@ROWCOUNT
--select * from @Elements
WHILE @cnt > 0
BEGIN
SELECT @colNm = value FROM @Elements WHERE Number = @cnt
SELECT @sqlStmt = 'INSERT INTO data_validation_log SELECT ' + '''' + @i_tab_nm + '''' + ', ' + '''' + @colNm + ''''
+ ', ' + @v_pkStr + ' FROM ' + @i_tab_nm + ' WHERE ' + @colNm + ' IS NULL OR ' + @colNm + ' = '''' ' ;
EXECUTE (@sqlStmt)
SET @cnt = @cnt -1
END
DROP TABLE ##tally
END TRY
BEGIN CATCH
SET @tmpMessage = 'Error ' + CAST(ERROR_NUMBER() AS VARCHAR) + ' error in stored procedure ' +
' at line ' + CAST(ERROR_LINE() AS VARCHAR) + ': ' + ERROR_MESSAGE()
RAISERROR(@tmpMessage, 15, 1);
-- Return a NON successful code ==> -1
SELECT @o_rtn_cd = -1
END CATCH
Best Regards,
~David
March 11, 2011 at 10:16 pm
The problem is right here
SELECT @v_pkStr = @v_pkStr
You define the variable but nothing is assigned to it so at this point it's value is NULL. NULL + anything will always be NULL.
The value should be what you expect it to be if you don't try to append a NULL string.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 11, 2011 at 10:41 pm
mtillman-921105 (3/11/2011)
Also, you should rewrite the query to use an INNER JOIN rather than the older way its using more than one table.
There's no performance gain in it. The only supposed advantage is that you can more easily tell if you've missed a join... Heh... I'm not buying that one either. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
March 14, 2011 at 8:27 am
Jeff Moden (3/11/2011)
mtillman-921105 (3/11/2011)
Also, you should rewrite the query to use an INNER JOIN rather than the older way its using more than one table.There's no performance gain in it. The only supposed advantage is that you can more easily tell if you've missed a join... Heh... I'm not buying that one either. 🙂
The reason is not performance. The older way of using multiple tables seperated by commas (rather than using JOINs) has been degraded and may not be supported in future versions of SQL. Or, at least that's my understanding. But maybe you know something I don't - has that changed?
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
March 14, 2011 at 8:59 am
This is correct and I have modified my code to adhere to the new join syntax. Thank you!
David
Best Regards,
~David
March 14, 2011 at 9:19 am
mtillman-921105 (3/14/2011)
Jeff Moden (3/11/2011)
mtillman-921105 (3/11/2011)
Also, you should rewrite the query to use an INNER JOIN rather than the older way its using more than one table.There's no performance gain in it. The only supposed advantage is that you can more easily tell if you've missed a join... Heh... I'm not buying that one either. 🙂
The reason is not performance. The older way of using multiple tables seperated by commas (rather than using JOINs) has been degraded and may not be supported in future versions of SQL. Or, at least that's my understanding. But maybe you know something I don't - has that changed?
Understood. It'll be interesting to see if they actually do that, though, because not supporting it would make the construction of correlated subqueries and Cross Apply's a whole lot different.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply