May 8, 2008 at 3:27 pm
I'm trying to get a count of rows for each column that has info in it. I have
DECLARE @cmd varchar(100)
DECLARE @fldname varchar(50)
declare @number int
-- the name of a each column is put into @fldname
SELECT @cmd = 'select count(*) from CONTACT2 where ' + @fldname + ' is not null '
-- now what I want it to do is to assign the results of exec(@cmd) into @number.
is this possible and if so how do i do it?
Thanks for the help,
Daniel
May 8, 2008 at 4:04 pm
You may do this way,
[font="Courier New"]DECLARE @cmd varchar(100)
DECLARE @fldname varchar(50)
DECLARE @number int
SET @fldname = 'ContactName'
CREATE TABLE #number (RecordCount INT)
SELECT @cmd = 'INSERT INTO #number SELECT COUNT(*) FROM CONTACT2 WHERE ' + @fldname + ' IS NOT NULL '
EXECUTE (@cmd)
SELECT @number = RecordCount FROM #number
SELECT @number
DROP TABLE #number[/font]
- Zahran -
May 8, 2008 at 5:18 pm
thanks it worked great
May 10, 2008 at 8:55 am
Execute dynamic SQL statement using sp_executesql system stored procedure, if you want a output to a variable. It allows input parameters as well as output parameters.
DECLARE @cmd NVARCHAR(100)
DECLARE @parm NVARCHAR(100)
DECLARE @fldname VARCHAR(50)
DECLARE @number INT
DECLARE @number_out INT
SET @fldname = 'Region'
SELECT @cmd = N'SELECT @number_out = COUNT(*) FROM Customers'
SELECT @cmd = @cmd + N' WHERE ' + @fldname + ' IS NOT NULL'
SELECT @parm = N'@number_out INT OUTPUT'
EXECUTE sp_executesql @cmd,@parm,@number_out = @number OUTPUT
SELECT @number
- Zahran -
May 10, 2008 at 2:36 pm
I support sp_executesql approach.
But the query IMHO is built not in the best way.
I must be like this:
'select count(' + QUOTENAME(@fldname) + ' ) from CONTACT2 '
_____________
Code for TallyGenerator
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply