building a SQL statement with column name a variable 1

[DougP]

my SP is being passed a column name in the variable named @Report and a name in @Manager, which then I am building this string.
set @SQLString = 'SELECT COUNT(*) FROM LDAPReportTree
Where ' + @Report + ' = ''' + @Manager + ''''

this is what it prints in the reusults window, which is the string itself.
SELECT COUNT(*) FROM LDAPReportTree Where Report1 = 'Flinstone, Fred'
I want it to return a 9
IF I run the string in its own Query window it returns the number 9

so how do I get 9 and not the String itself?

TIA

DougP

 

[djj55]

Are you doing an Execute? See sp_executesql in BOL.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[DougP]

Ok I managerd to get this far
But getting error on EXEC
Error = Incorrect syntax near the keyword 'EXEC'.

	set @SQLString = N'SELECT COUNT(*) FROM LDAPReportTree 
				Where ' + @Report + ' = ''' + @Manager + ''''
         Set @Total = [highlight #FCE94F]EXEC[/highlight] sp_executesql @SQLString
         Set @Empl= EXEC sp_executesql @SQLStringEmpl  
	Set @FullString = (@Total + "|" + @Empl)
	Select 		@FullString

So if I remove teh SEt @Total I get a 9 in the results window.
But now I need it to return the 9 and another value in a string like so
9|5
so this is now the result and now just a number

DougP

 

[DaveInIowa]

Try this:

DECLARE @Count INT

SET @SQLString = 'SELECT @Count = COUNT(*) FROM LDAPReportTree WHERE ' + @Report + '=' + QUOTENAME(@Manager, '''')

EXECUTE sp_executesql @SQLString, N'@Count INT OUTPUT', @Count OUTPUT

SELECT @Count As RecordCount

 

[DougP]

Getting closer but the @Total is now NULL

Declare @Total as nvarchar(4)
set @SQLString = N'SELECT COUNT(*) FROM LDAPReportTree 
				Where ' + @Report + ' = ''' + @Manager + ''''
exec sp_executesql @SQLString, @Total Out 
Select 	@Total

the results windows has 2 lines, the first conatins a 9 the second contains a NULL

DougP

 

[DougP]

DaveInIowa thanks that worked, have a star
I did not see it when I posted my last one or it was not there ???

DougP