Problem in writing SQL stored procedure

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

SQL

CREATE PROCEDURE [dbo].[sp_empInfo]
@empID int =null, 
@TesterName varchar(250)=null
as 
declare @strSql nvarchar(4000)

			SET	@strSql='Declare  users_cursor CURSOR FOR SELECT ID,TesterName FROM emp Where Status=1 '
				IF @empID IS NOT NULL AND LEN(@empID) > 0
					SET	@strSql = @strSql +' AND ID='+CAST(@empID AS NVARCHAR(10))
				IF @TesterName IS NOT NULL AND LEN(@TesterName) > 0
					SET	@strSql = @strSql +' AND TesterName='+@TesterName
		exec sp_executesql @strSql
   		open   users_cursor
				fetch next from users_cursor into @empID, @TesterName
						while @@FETCH_STATUS = 0 BEGIN
						---PROCESS
			    fetch next from users_cursor into @empID, @TesterName
	 	END
	 	close users_cursor
		deallocate users_cursor

when i pass TesterName as Nil I'm getting Error 
Invalid column name 'Nil'

What I have tried:

Tried Appending N i.e. SET @strSql=N'Declare
but didn't work

Posted 21-Jul-16 20:59pm

kedar001

Updated 21-Jul-16 21:21pm

v2

Add a Solution

Comments

[no name] 22-Jul-16 3:07am

Is there maybe simply a space missing in ' AND ID=' instead of ' AND ID= ' and also here ' AND TesterName='?

kedar001 22-Jul-16 3:18am

NOPE Tried with adding Space,but didn't work
SET @strSql = @strSql +' AND TesterName= '''+@TesterName+''''
this is Working but is this right way ?

[no name] 22-Jul-16 3:22am

I see. Yes this is the right way, having the value of @TesterName in single quotes.

pt1401 22-Jul-16 3:41am

It isn't the right way, because it's horribly inefficient.
Try:-

IF @empID IS NOT NULL AND LEN(@empID) > 0
SELECT ID, TesterName FROM emp Where Status=1 AND ID=@empID
ELSE IF @TesterName IS NOT NULL AND LEN(@TesterName) > 0
SELECT ID, TesterName FROM emp Where Status=1 AND TesterName=@TesterName

Ah ok, that doesn't work when you supply both @empID & @TesterName

[no name] 22-Jul-16 3:44am

And why you don't write this in your answer?

pt1401 22-Jul-16 3:45am

Because the question was 'why am I getting an invalid column name error' :)

[no name] 22-Jul-16 3:48am

So same for me, to solve 'why am ...' it is the 'right way' :)

pt1401 22-Jul-16 4:41am

So I assume the cursor is needed, and there will be something the --PROCESS loop?
If that's the case, I'd write the proc as
CREATE PROCEDURE [dbo].[sp_empInfo]
@empID int =null,
@TesterName varchar(250)=null
AS

DECLARE @emp INT
DECLARE @tester VARCHAR(250)

DECLARE users_cursor CURSOR FAST_FORWARD READ_ONLY FOR
SELECT @emp=ID, @tester=TesterName
FROM emp
WHERE Status=1
AND (@empID IS NULL OR @empId='' OR ID=@empID)
AND (@TesterName IS NULL OR @TesterName='' OR @TesterName=TesterName)

OPEN users_cursor
FETCH NEXT FROM users_cursor INTO @emp, @tester

WHILE @@FETCH_STATUS = 0
BEGIN
-- PROCESS
FETCH NEXT FROM users_cursor INTO @emp, @tester
END

CLOSE users_cursor
DEALLOCATE users_cursor

If the cursor really isn't needed, it reduces to
CREATE PROCEDURE [dbo].[sp_empInfo]
@empID int =null,
@TesterName varchar(250)=null
AS

SELECT @emp=ID, @tester=TesterName
FROM emp
WHERE Status=1
AND (@empID IS NULL OR @empId='' OR ID=@empID)
AND (@TesterName IS NULL OR @TesterName='' OR @TesterName=TesterName)

There's a place for dynamic sql, but it's better no used if it can be avoided.
UPDATE: SELECT @emp=ID, @tester=TesterName
SHOULD BE SELECT ID, TesterName

kedar001 22-Jul-16 4:44am

ok thanks for reply.
but when i append SQL string to @strSql i'am getting Error as Invalid column name 'Nil'.

as SET @strSql = @strSql +' AND TesterName= '''+@TesterName+''''
inefficient ,how i can write it efficiently.

pt1401 22-Jul-16 4:46am

You don't need strSql, use either or the proc definitions above

kedar001 22-Jul-16 4:49am

when @TesterName is null then i don't want to include it in Query
thats why i have use @strSql

pt1401 22-Jul-16 4:54am

Have you tried running the proc as above and comparing the resultset to the original?

kedar001 22-Jul-16 5:11am

Thanks
Done

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

pt1401 · Answer 1 · 2016-07-21T21:21:00

Solution 1

SET @strSql = @strSql +' AND TesterName='+@TesterName

should be

SET @strSql = @strSql +' AND TesterName='''+@TesterName + ''''

Posted 21-Jul-16 21:21pm

pt1401

Problem in writing SQL stored procedure

1 solution

Solution 1

Add your solution here

Preview 0

Problem in writing SQL stored procedure

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us