Using Stored Procedure or Dynamic SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Using Stored Procedure or Dynamic SQL

Author

Topic

Buzzard724
Yak Posting Veteran

66 Posts

Posted - 2010-03-29 : 15:12:56

I have a table which contains the names of fields that I want to Audit. The SQL at the end of this post works well in returning the first field name and then using it construct a query. However the following script returns literally nothing. Please can you suggest a better syntax - and perhaps how to place in a Stored Procedure? thanks

DECLARE @TableName VarChar(100) SET @TableName = 'Customers'
DECLARE @FieldNames VarChar(1000)
DECLARE @Length int
DECLARE @SQL VarChar(1000)
--find the field names
SELECT @FieldNames = @FieldNames + COALESCE(FieldNameToAudit + ', ','')
FROM FieldsToAudit
WHERE TableNameToAudit = 'Customers'
--remove the final comma
SET @Length = DATALENGTH(@FieldNames)
IF (@Length > 0)
BEGIN
SET @FieldNames = SUBSTRING(@FieldNames,1, DATALENGTH(@FieldNames) - 2)
END
--construct the select statement
SELECT @SQL = 'SELECT ' + @FieldNames
SELECT @SQL = @SQL + ' FROM '
SELECT @SQL = @SQL + @TableName

Exec ( @SQL)

AND the version that partly works (it only returns one field name)

DECLARE @TableName VarChar(100) SET @TableName = 'Customers'
DECLARE @FieldNames VarChar(1000)
DECLARE @Length int
SELECT TOP 1 @FieldNames = FieldNameToAudit
FROM FieldsToAudit
WHERE TableNameToAudit = 'Customers'
--PRINT @FieldNames
DECLARE @SQL VarChar(1000)
SELECT @SQL = 'SELECT ' + @FieldNames
SELECT @SQL = @SQL + ' FROM '
SELECT @SQL = @SQL + @TableName

Exec ( @SQL)

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-29 : 15:28:48

You need to initialize the @FieldNames. Otherwise, it's NULL, and every time you concatenate onto it, ti remains NULL. Remember, NULL + 'anything' results in NULL.

SELECT @FieldNames = ''

There are 10 types of people in the world, those that understand binary, and those that don't.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-03-29 : 15:29:36

It looks like you need to Initialize the @FieldNames variable: NULL + Someting = NULL.

Dang too slow.. :)

Buzzard724
Yak Posting Veteran

66 Posts

Posted - 2010-03-29 : 16:02:08

Thank you - I have added
DECLARE @FieldNames VarChar(1000) SET @FieldNames = ''

I now want to create a Table-Valued Function
So I have created a Stored Procedure (as at the end of the post) but the syntax for the the function is defeating me

CREATE FUNCTION [dbo].[Test]()

RETURNS TABLE AS
[dbo].[SPOC_FieldsToAudit]
RETURN

The stored procedure was created as follows:
CREATE PROC [dbo].[SPOC_FieldsToAudit]
AS

SET NOCOUNT ON

DECLARE @TableName VarChar(100) SET @TableName = 'Customers'
DECLARE @FieldNames VarChar(1000) SET @FieldNames = ''
...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources