January 28, 2009 at 4:16 am
say this code
@y = 'name' --- column name
@z int ---- the output
sq_executesql 'select count(distinct @y) from table_Name', @y , @z output
select @z
give error
why any help I can't understand anythign from MSDN site
January 28, 2009 at 4:50 am
-- *** Test Data ***
CREATE TABLE test
(
    [Name] varchar(20) NOT NULL
)
INSERT INTO test
SELECT 'Name1' UNION ALL
SELECT 'Name2' UNION ALL
SELECT 'Name1' UNION ALL
SELECT 'Name2'
-- *** End Test Data ***
DECLARE @y varchar(20)
    ,@z int
    ,@SQLString nvarchar(4000)
SET @y = '[Name]'
SET @SQLString = N'SELECT @n = COUNT(DISTINCT ' + @y + N') FROM test'
EXEC sp_executesql @SQLString, N'@n int OUTPUT', @z OUTPUT
SELECT @z
DROP TABLE test
January 28, 2009 at 4:57 am
You can't pass parameters as column names, that's the error you're no doubt getting. To do something like this, you need to build the string and execute it:
DECLARE @y nvarchar(50) = 'name',@z int
,@sql nvarchar(max)
,@parm nvarchar(max)
SET @sql = 'SELECT @z = COUNT(DISTINCT ' + @y + ')FROM table_Name'
SET @parm = '@z int OUTPUT'
EXEC sp_executesql @sql,@parm,@Z = @z OUTPUT
SELECT @z
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 28, 2009 at 4:58 am
Oops. Right. Forgot the N in front of the strings... sorry.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply