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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy