May 31, 2016 at 7:58 am
Dear Team,
We are getting error while executing below code. Please help.
SET QUOTED_IDENTIFIER OFF
DECLARE @value5 INT
declare @qry varchar(500)
DECLARE @value4 varchar(20)
DECLARE @database_name varchar(50)
set @database_name = 'testdb1'
set @qry = 'SELECT @value4= COUNT(*) FROM '+@database_name+'..test_table'
exec (@qry)
Error :- Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@value4".
May 31, 2016 at 8:26 am
In the scope of your Exec statement, "exec (@qry)", @value4 is not declared. It's declared outside of that.
Take a look at sp_executesql, instead of exec, for running dynamic SQL with parameters.
https://msdn.microsoft.com/en-us/library/ms188001.aspx
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 31, 2016 at 8:26 am
You're declaring the variable outside of the dynamic SQL, hence it's out of scope inside the dynamic SQL.
Since it's intended to be an output parameter, you'll need to use sp_executeSQL rather than EXEC, declare the parameter and then pass it with the OUTPUT clause to get it out.
Oh, and depending where and how this is used, you probably have a severe SQL injection vulnerability that you should fix before it's used to steal data or drop databases.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 31, 2016 at 8:58 am
As mentioned, I wouldn't recommend this technique. It looks like you're trying to write one routine to get counts from any database. Or are you looking for any table? You could do something more like this:
CREATE PROCEDURE GetCount @name VARCHAR(200)
AS
BEGIN
WITH cteCount ( name, rcount )
AS ( SELECT
o.name
, ddps.row_count
FROM
sys.indexes AS i
INNER JOIN sys.objects AS o
ON
i.object_id = o.object_id
INNER JOIN sys.dm_db_partition_stats AS ddps
ON
i.object_id = ddps.object_id
AND i.index_id = ddps.index_id
WHERE
i.index_id < 2
AND o.is_ms_shipped = 0
)
SELECT
c.name
, c.rcount
FROM
cteCount c
WHERE
c.name = @name;
END;
GetCount 'Articles'
June 1, 2016 at 2:56 am
Thank you. The problem has been resolve.
This is helpful link.
June 1, 2016 at 9:01 pm
You just put the declares in the @qry
set @qry = 'Declare @value4 int, @database_name varchar(100) SELECT @value4= COUNT(*) FROM '+@database_name+'..test_table'
The parser will be able to sort it out.
-CodeMan
June 1, 2016 at 11:14 pm
Below code is working fine now. Thanks to all for suggest solutions.
DECLARE @count1 NVARCHAR(20)
DECLARE @qry4 NVARCHAR(1000)
DECLARE @qry5 NVARCHAR(1000)
DECLARE @ParmDefinition NVARCHAR(500)
SET @qry4 = 'SELECT @countOUT = COUNT(*) FROM '+@file_name1+'..WebProxyLog'
SET @ParmDefinition = N'@countOUT NVARCHAR(20) OUTPUT'
EXEC SP_EXECUTESQL @qry4,@ParmDefinition,@countOUT = @count1 OUTPUT
June 2, 2016 at 3:14 am
--method 1
declare @qry nvarchar(500)
DECLARE @database_name varchar(50)
set @database_name = 'MASTER'
set @qry ='Select count(*) [count] from ' +@database_name+'..sysdatabases'
create table #temp (cnt int)
insert #temp
exec (@qry)
select * from #temp
drop table #temp
--Method 2
DECLARE @value5 INT
declare @qry nvarchar(500)
DECLARE @value4 nvarchar(20)
declare @output nvarchar(500)
DECLARE @database_name varchar(50)
set @database_name = 'MASTER'
set @qry ='Select @value5=count(*) from ' +@database_name+'..sysdatabases'
SET @output = N'@value5 nvarchar(20) OUTPUT'
EXECUTE SP_EXECUTESQL @qry ,@output,@value5 = @value4 output
select @value4
June 2, 2016 at 4:04 am
You still have a severe SQL Injection vulnerability that you need to fix before your company becomes the next 'data breech' headline.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 2, 2016 at 8:40 am
GilaMonster (6/2/2016)
You still have a severe SQL Injection vulnerability that you need to fix before your company becomes the next 'data breech' headline.
+1
If you don't know what it is, please ask.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply