Stored Procedure Parameter Problem
-
Hi guys
I created one procedure which is like this
CREATE Procedure select_Proc1
@Key3 varchar(10)
As
BEGIN
Select key3
From Or_Table
Where
END
GO
now when i execute this procedure without pass any parameter it gives me an error, what can i do when i want to see all data.
its urgent
thanx in advance
-
CREATE Procedure select_Proc1
@Key3 varchar(10)
As
BEGIN
IF (@key3 IS NULL)
BEGIN
Select key3
From Or_Table
END
ELSE
BEGIN
Select key3
From Or_Table
Where
END
END
And then again, I might be wrong ...
David Webb -
hi david,
thanx for ur solution,
i solved it through this procedure
CREATE Procedure select_Proc1
@Key3 varchar(10) ='%'
As
BEGIN
Select key3
From Or_Table
Where
(key3 like @key3)
END
GO
thanx again
-
I'n not a big fan of using "LIKE". It slows down you queries when working wit hlarge amounts of data. This is what I would do:
CREATE Procedure select_Proc1
@Key3 varchar(10)
As
BEGIN
SELECT
tbl.key3
FROM
Or_Table tbl
WHERE
(tbl.key3 = isNULL(@key3, tbl.key3)
END
GO
-
One minor correction
CREATE
Procedure select_Proc1
@Key3 varchar(10) = NULL
....
If you don't give the parameter a default value, then it's mandatory and SQL will throw an error if the sp is called without it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thanx to u all
i really did not want to use like
thanx again
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply