parameters in SP

CREATE PROC xxx

@.user VARCHAR(15),

@.rank varCHAR(10) AS

DECLARE @.sql VARCHAR(100)

SET @.sql = 'SELECT ' + @.user + ' FROM usertable where grade = ' + @.rank

EXEC (@.sql)

GO

when i execute this proc without where condintion its working, but when i use where condition its dispalyin invalid column name with the name im passing

eg.

xxx admin,aB

WHEN I TRY TO EXECUTE PROC WITH ABOVE STAT, ITS DIAPLAYIN ERROR AS "INVALID COLUMN NAME ab

but

xxx admin," ' aB ' "

when i try like this its giving result.

how can i avoid second method of executin the proc and use first method for the sake of passing value from frontend

change your sql query as..

SET @.sql = 'SELECT ' + @.user + ' FROM usertable where grade =''' + @.rank +'''' [...i added 3 single quotes before + @.rank and 4 single quotes after @.rank +...]

it would work..

CREATE PROC xxx

@.user VARCHAR(15),

@.rank varCHAR(10) AS

DECLARE @.sql VARCHAR(100)

SET @.sql = 'SELECT ' + @.user + ' FROM usertable where grade =''' + @.rank +''''

EXEC (@.sql)

GO

Good Luck./.

|||

Hi,

Your Procedure should be like the following

CREATE PROC xxx

@.user VARCHAR(15),

@.rank varCHAR(10) AS

DECLARE @.sql VARCHAR(100)

SET @.sql = 'SELECT ' + @.user + ' FROM usertable wheregrade = ''' + @.rank + ''''

EXEC (@.sql)

GO

Now Execute your query. It will not raise the error.

I hope this is useful to you.

Cheers,
Ganesh.

|||

Thanks for the responses.

But what logic is this? 3 quotes before and 4 quotes after??

any way i will figure it out.

Thankyou again