Using parameters inside a SP
-
Hello
This is a stored procedure i am trying to get working:
create procedure Test @statusID int = 0
AS
declare @select as nvarchar(100)
set @select = 'select * from tblStatus'
declare @where as nvarchar(100)
if @statusid > 0
set @Where = ' where fldstatus = ' + @statusID
@select + @where
I am trying to see the results of a statement like this:
select * from tblStatus where fldStatus = 2
for example, but i don't, cause the syntax is wrong. How can i accomplish this?
Thanks
-
You don't :
CREATE PROCEDURE dbo.Test @StatusID AS INT = 0
AS
SET NOCOUNT ON
Select List, The, ColNames, Here from dbo.tblStatus where fldStats = @StatusID
SET NOCOUNT OFF
-
Try the below:
CREATE PROCEDURE Test
@statusID int = 0
AS
DECLARE @select as nvarchar(100)
DECLARE @where as nvarchar(100)
SET @select = 'select * from tblStatus'
IF @statusid > 0
SET @Where = ' where fldstatus = ' + @statusID
SET @select = @select + @where
EXEC(@SELECT)
GO
-
I get this after running your script:
Server: Msg 245, Level 16, State 1, Procedure Test, Line 10
Syntax error converting the varchar value ' where fldstatus = ' to a column of data type int.
So i wrote it like this:
create PROCEDURE Test
@statusID int = 0
AS
DECLARE @select as nvarchar(100)
DECLARE @where as nvarchar(100)
SET @select = 'select * from tblStatus'
IF @statusid > 0
SET @Where = ' where fldstatus = ' + cast(@statusID as nvarchar(100))
SET @select = @select + @where
EXEC(@SELECT)
and it worked!! thanks for the posts guys
-
You don't get what I said .
DON'T FÛQUYNG DO THAT!!!!!!!!!
Unless you want your server a prime suspect for hacking.
-
CREATE PROCEDURE Test
@statusID int = 0
AS
SELECT * FROM tblStatus
WHERE
fldstatus = CASE WHEN @statusID = 0 THEN fldstatus ELSE @statusID END
GO
/*******************************************
As mentioned by rqR'us ??????????!!!!!!!(R*m*)
Will work there is no need for dynamic SQL.
********************************************/
Regards,
gova -
ok ok!! let me read the article and i'll be back
-
"As mentioned by rqR'us ??????????!!!!!!!(R*m*)"
-
Got the point...sent the link to my boss....up to him to decide now! i'm just a humble soldier...thanks everybody and you Mr with the funny nickname for the link
-
The resident genious ubiquitous spook welcomes you
. -
) Congradulations for the new name.Regards,
gova -
Sushila came up with it as a joke, but now I'm keeping it. So the joke's on her
.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply