Must Declare Variable error....
-
Hi,
Please look at code Snippets given below. When I run it gives me 'Must declare the variable '@count' ERROR.
Create Procedure XYZ
(
@name varchar(50)
@Last varchar(50)
)
AS
DECLARE @count int
DECLARE @strQuery varchar(4096)
SET @strQuery = 'Select @count = count(*) from app_client where first_name = ''' + @name + ''' AND last_name = ''' + @Last + ''''
EXEC (@strQuery)
IF @count = 0
BEGIN
SELECT 'Not Found'
END
GO
Please Help...
-
Your variable is out of scope hence the problem
Why not simply put
SELECT @Count = COUNT(*)
FROM app_client
WHERE first_name = @name
AND last_name = @Last
-
if one were to go only by the code snippet provided then it can all be condensed to...
IF NOT EXISTS(SELECT * FROM app_client WHERE first_name = @name AND last_name = @last) BEGIN SELECT 'Not Found' END..(can't see the need for dynamic sql)
**ASCII stupid question, get a stupid ANSI !!!** -
sushila
SELECT *
Bad practice
I would do
IF NOT EXISTS(SELECT 1 FROM app_client WHERE first_name = @name AND last_name = @Last)
BEGIN
SELECT 'Not Found'
END
Far away is close at hand in the images of elsewhere.
Anon. -
Makes no difference in a exists clause.
-
Thank you remi - so David - there's your answer - mine was actually a trap to lure in those who hadn't known about the * & exists combo...GOTCHA!
**ASCII stupid question, get a stupid ANSI !!!** -
Hi,
Sorry for wrongly defining problem. I apololize for it.
Let me redefine it again.
The parameter of below SP gets the value as below:
First_name = 'vishal' And last_name = 'Vapi'
Create Procedure XYZ
(
@conditionstring varchar(4096)
)
AS
DECLARE @count int
DECLARE @strQuery varchar(4096)
SET @strQuery = 'Select @count = count(*) from app_client where + @conditionstring
EXEC (@strQuery)
IF @count = 0
BEGIN
SELECT 'Not Found'
END
GO
This gives me Error as "Must Declare the Variable".
-
I thought the answer has been given. The error happens during the execution of the dynamic sql !!!!!!!!!!
-
Create Procedure XYZ
(
@conditionstring nvarchar(3950)
)
AS
DECLARE @count int
DECLARE @strQuery nvarchar(4000)
SET @strQuery = 'Select @count = count(*) from app_client where ' + @conditionstring
EXEC sp_executesql @strQuery, N'@count int OUTPUT', @count OUTPUT
IF @count = 0
BEGIN
SELECT 'Not Found'
END
GO
Far away is close at hand in the images of elsewhere.
Anon. -
Oh..., those "alluring" traps! [Good one]
I wasn't born stupid - I had to study.
-
or
SET @strQuery = 'IF EXISTS(select 1 from app_client where ' + @conditionstring + ') SET @count=1'
to avoid using count(*) to improve performance
Far away is close at hand in the images of elsewhere.
Anon. -
You meant select *?
. -
I think he's too "set in his ways"...
**ASCII stupid question, get a stupid ANSI !!!** -
oooops! forgot to initialise the variable
need to add
SET @count = 0
before sp_executesql
Far away is close at hand in the images of elsewhere.
Anon. -
keep getting sidetracked - meant to tell Vishal that if he posts the complete requirements maybe someone'll come up with a solution that does not involve dynamic sql at all...remi hasn't done so yet (or for that matter Farrell) - so I'll post the link to this much traversed path...The curse & blessings of dynamic sql
**ASCII stupid question, get a stupid ANSI !!!**
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply