How to use if condition in stored procedures
-
Hi, I want to develop a stored procedure in which I have to apply different modifications to the Query. Finally after undergoing all the changes to the query I have to execute the final query and get the resultant table.For example: Create Procedure [dbo].[Test] @Flg1 bit, @Flg2 bit, @Flg3 bit, AS BEGIN SET NOCOUNT ON; Declare @Query varchar(max) if(@Flg1<>0) Begin Set @Query='Select * from emplayee where empId=1" End if(@Flg2<>0) Begin Set @Query=@Query+'and empId=2' End if(@Flg3<>0) Begin Set @Query=@Query+'and empId=3' End Exec sp_executesql(@Query); End As shown above my Query conditions will change according to the parameters. But here I am not able to get the resultant table. Please reply me if anyone know how to get the resultant Table. Also please tell me whether the above procedure is correct or not. Thanks in advance.
-
Hi, I want to develop a stored procedure in which I have to apply different modifications to the Query. Finally after undergoing all the changes to the query I have to execute the final query and get the resultant table.For example: Create Procedure [dbo].[Test] @Flg1 bit, @Flg2 bit, @Flg3 bit, AS BEGIN SET NOCOUNT ON; Declare @Query varchar(max) if(@Flg1<>0) Begin Set @Query='Select * from emplayee where empId=1" End if(@Flg2<>0) Begin Set @Query=@Query+'and empId=2' End if(@Flg3<>0) Begin Set @Query=@Query+'and empId=3' End Exec sp_executesql(@Query); End As shown above my Query conditions will change according to the parameters. But here I am not able to get the resultant table. Please reply me if anyone know how to get the resultant Table. Also please tell me whether the above procedure is correct or not. Thanks in advance.
Hi, You have mistakes ...
Nekkantidivya wrote:
Set @Query='Select * from emplayee where empId=1"
You have ended up with double quotes.
Nekkantidivya wrote:
Declare @Query varchar(max)
Replace 'max' with some number like 200 as your string cant go over this. Correct it n then check.
modified on Saturday, April 18, 2009 7:24 AM
-
Hi, I want to develop a stored procedure in which I have to apply different modifications to the Query. Finally after undergoing all the changes to the query I have to execute the final query and get the resultant table.For example: Create Procedure [dbo].[Test] @Flg1 bit, @Flg2 bit, @Flg3 bit, AS BEGIN SET NOCOUNT ON; Declare @Query varchar(max) if(@Flg1<>0) Begin Set @Query='Select * from emplayee where empId=1" End if(@Flg2<>0) Begin Set @Query=@Query+'and empId=2' End if(@Flg3<>0) Begin Set @Query=@Query+'and empId=3' End Exec sp_executesql(@Query); End As shown above my Query conditions will change according to the parameters. But here I am not able to get the resultant table. Please reply me if anyone know how to get the resultant Table. Also please tell me whether the above procedure is correct or not. Thanks in advance.
Create Procedure [dbo].[Test] @EmpID Int as AS BEGIN SET NOCOUNT ON Select * from emplayee where empId <= @EmpID END You better ask these question in the SQL part of this forum. They'll give you more proper answers.
In Word you can only store 2 bytes. That is why I use Writer.