April 14, 2008 at 9:39 am
I'm trying to fetch a dataset containing values from the Description field of my table.
Basically, I need to work out which fields are mandatory (denoted by a 'D' in the description field) then generate a dynamic SQL string based only on those fields which report as being mandatory and finally determine which of the fields returned as a result of the query have null values in them.
So far, I've managed to get the dynamic SQL string generated to return all of the fields that are 'mandatory' with the associated values, but now I'm stuck as to how to return only the fields that have a NULL value.
I was hoping that I could get VB.NET to finish off this code, but it's refusing to see that my sproc returns anything despite there being output when I run the sproc in SQL enterprise manager.
Can anyone help me out on how to finish this? I was wondering if there was some way I could insert the result of the dynamic SQL string into a temporary table and work on that but obviously I don't know how many columns the dynamic SQL string will return...
Or am I trying to do something impossible???
CREATE PROCEDURE [dbo].[usp_Fetch_DCF_Fields]
@TableName varchar(50),
@RecordID varchar(6),
@KeyColumnName varchar(50)
AS
--create a temporary table into which the mandatory columns are selected
DECLARE @Columns TABLE
(ColumnName varchar (250))
INSERT INTO @Columns
SELECT objname
FROM fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', @TableName, 'column', default)
WHERE NAME = 'MS_Description' and VALUE = 'D'
SET ROWCOUNT 0
SET ROWCOUNT 1
DECLARE @ColumnName varchar(250)
SELECT @ColumnName = ColumnName FROM @Columns
DECLARE @SQLStatement varchar(4000)
SET @SQLStatement = 'SELECT '
WHILE @@rowcount <> 0
BEGIN
SET rowcount 0
SET @ColumnName = (SELECT * FROM @Columns WHERE ColumnName = @ColumnName)
SET @SQLStatement = @SQLStatement + @ColumnName + ', '
DELETE @Columns WHERE ColumnName = @ColumnName
SET ROWCOUNT 1
SELECT @ColumnName = ColumnName FROM @Columns
END
SET ROWCOUNT 0
SET @SQLStatement = SUBSTRING(@SQLStatement,1, LEN(@SQLStatement)-1)
SET @SQLStatement = @SQLStatement + ' FROM ' + @TableName + ' WHERE ' + @KeyColumnName + ' = ' + @RecordID
EXECUTE(@SQLstatement)
DROP TABLE @Columns
April 14, 2008 at 10:29 am
rebecca.crowdey (4/14/2008)
I'm trying to fetch a dataset containing values from the Description field of my table....snip...
CREATE PROCEDURE [dbo].[usp_Fetch_DCF_Fields]
@TableName varchar(50),
@RecordID varchar(6),
@KeyColumnName varchar(50)
AS
--create a temporary table into which the mandatory columns are selected
DECLARE @Columns TABLE
(ColumnName varchar (250))
INSERT INTO @Columns
SELECT objname
FROM fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', @TableName, 'column', default)
WHERE NAME = 'MS_Description' and VALUE = 'D'
SET ROWCOUNT 0
SET ROWCOUNT 1
DECLARE @ColumnName varchar(250)
SELECT @ColumnName = ColumnName FROM @Columns
DECLARE @SQLStatement varchar(4000)
SET @SQLStatement = 'SELECT '
WHILE @@rowcount <> 0
BEGIN
SET rowcount 0
SET @ColumnName = (SELECT * FROM @Columns WHERE ColumnName = @ColumnName)
SET @SQLStatement = @SQLStatement + @ColumnName + ', '
DELETE @Columns WHERE ColumnName = @ColumnName
SET ROWCOUNT 1
SELECT @ColumnName = ColumnName FROM @Columns
END
SET ROWCOUNT 0
SET @SQLStatement = SUBSTRING(@SQLStatement,1, LEN(@SQLStatement)-1)
SET @SQLStatement = @SQLStatement + ' FROM ' + @TableName + ' WHERE ' + @KeyColumnName + ' = ' + @RecordID
EXECUTE(@SQLstatement)
DROP TABLE @Columns
try this simplified version:
CREATE PROCEDURE [dbo].[usp_Fetch_DCF_Fields]
@TableName varchar(50),
@RecordID varchar(6),
@KeyColumnName varchar(50)
AS
DECLARE @cols varchar(max)
SET @cols = ''
SELECT @cols = @cols + ', '+ objname
FROM dbo.fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', @TableName, 'column', default)
WHERE NAME = 'MS_Description' and VALUE = 'D'
SET @cols = STUFF(@cols, 1, 2, '') -- drop leading ,
DECLARE @SQLStatement varchar(max)
SET @SQLStatement = @cols + ' FROM ' + @TableName + ' WHERE ' + @KeyColumnName + ' = ' + @RecordID
EXECUTE(@SQLstatement)
April 15, 2008 at 3:59 am
This little snippet might help
select * into #temp1
from [add your source objects here]
where 1=2
select * from tempdb.dbo.syscolumns
where id = (select id from tempdb.dbo.sysobjects where name like '#temp1%')
drop table #temp1
I was hoping that I could get VB.NET to finish off this code, but it's refusing to see that my sproc returns anything despite there being output when I run the sproc in SQL enterprise manager.
In the work i've done with SSIS there can be an issue with longer stored procedures where the SSIS component won't recognise the output when validating, even though testing the output works.
There was a little hack for this, which might work for you too
April 15, 2008 at 4:28 am
Thanks for your replies guys.
The lack of output problem was because I was testing it under my windows account in SQL Enterprise Manager (or whatever it's called in 2k5) but the VBCode was calling it using a SQL account that didn't have SELECT permissions on the underlying table.
This code finally achieved what I was trying to do completely in SQL, but it's not pretty
IF EXISTS (
SELECT name
FROM sysobjects
WHEREname = N'usp_Fetch_DCF_Fields'
ANDtype = 'P')
DROP PROCEDURE usp_Fetch_DCF_Fields
GO
CREATE PROCEDURE [dbo].[usp_Fetch_DCF_Fields]
@TableName varchar(50),
@RecordID varchar(6),
@KeyColumnName varchar(50)
AS
--create a temporary table into which the mandatory columns are selected
DECLARE @Columns TABLE
(ColumnName varchar (250))
INSERT INTO @Columns
SELECT objname
FROM fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', @TableName, 'column', default)
WHERE NAME = 'MS_Description' and VALUE = 'D'
SET ROWCOUNT 0
SET ROWCOUNT 1
DECLARE @ColumnName varchar(250)
SELECT @ColumnName = ColumnName FROM @Columns
DECLARE @SQLStatement nvarchar(4000)
SET @SQLStatement = 'SELECT '
DECLARE @DCFItems TABLE
(ColumnName varchar(250))
WHILE @@rowcount <> 0
BEGIN
SET rowcount 0
SET @ColumnName = (SELECT * FROM @Columns WHERE ColumnName = @ColumnName)
SET @SQLStatement ='SELECT @ValueOut=' + @ColumnName + ' FROM ' + @TableName + ' WHERE ' + @KeyColumnName + ' = ' + @RecordID
DECLARE @ParmDefinition nvarchar(100)
SET @ParmDefinition = N'@ValueOut varchar(100) OUTPUT'
DECLARE @Value varchar(250)
EXECUTE sp_executesql
@SQLStatement,
@ParmDefinition,
@ValueOut=@Value OUTPUT
IF @Value IS NULL
BEGIN
INSERT INTO @DCFItems(ColumnName) VALUES (@ColumnName)
END
DELETE @Columns WHERE ColumnName = @ColumnName
SET ROWCOUNT 1
SELECT @ColumnName = ColumnName FROM @Columns
END
SET ROWCOUNT 0
SELECT ColumnName FROM @DCFItems
GO
GRANT EXECUTE ON usp_Fetch_DCF_Fields TO PUBLIC
GO
April 15, 2008 at 10:28 am
I'm confused as to why you want to return column names that have a NULL value. Almost any version of VB (whether .NET based or still using ADO) can read column values and column names from a recordset. So, why not just return the mandatory columns of the row with the dynamic SQL and let the VB code skip/ignore the columns with non-null values?
CREATE PROCEDURE [dbo].[usp_Fetch_DCF_Fields]
@TableName varchar(50),
@RecordID varchar(6),
@KeyColumnName varchar(50)
AS
DECLARE @cols varchar(max)
SET @cols = ''
SELECT @cols = @cols + ', '+ objname
FROM dbo.fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', @TableName, 'column', default)
WHERE NAME = 'MS_Description' and VALUE = 'D'
SET @cols = STUFF(@cols, 1, 2, '') -- drop leading ,
DECLARE @SQLStatement varchar(max)
SET @SQLStatement = 'SELECT '+ @cols + ' FROM ' + @TableName + ' WHERE ' + @KeyColumnName + ' = ' + @RecordID
EXECUTE(@SQLstatement)
If you really just want a list of mandatory columns names that have null values, use this code and use VB's Split() function to convert the string into an array.
CREATE PROCEDURE [dbo].[usp_Fetch_DCF_Fields]
@TableName varchar(50),
@RecordID varchar(6),
@KeyColumnName varchar(50)
AS
DECLARE @cols varchar(max), @C char(1), @q char(1)
SET @C = ','
SET @q = ''''
SET @cols = ''
select @cols = @cols+' +'+
'(case when '+ objname+ ' is null then '+ @q+@c+objname+@q+ ' else '+@q+@q+' end)'
FROM dbo.fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', @TableName, 'column', default)
WHERE NAME = 'MS_Description' and VALUE = 'D'
SET @cols = STUFF(@cols, 1, 2, '') -- drop leading +
DECLARE @SQLStatement varchar(max)
SET @SQLStatement = 'SELECT '+ @cols + ' FROM ' + @TableName + ' WHERE ' + @KeyColumnName + ' = ' + @RecordID
EXECUTE(@SQLstatement)
April 15, 2008 at 10:44 am
As I said in my earlier post, I was having a problem with .NET not returning anything back into my dataset despite the fact that the sproc was retrieving values when I ran it in SSMS. It was due to my .NET app using a SQL account to access the sproc and that SQL account not having SELECT permissions on the table I was interrogating. Once I'd figured that out and the data was coming back into my dataset, then I was able to go with my original plan which was pretty much what you suggested antonio.
I'm not really a Transact SQL person, as long as I can read and write to and from VB I try to steer clear of SQL as much as possible but it was a new departure for me to figure out how I could get what I wanted in .NET straight from my proc without needing .NET to massage it further.
Thanks for the pointers 😎
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply