tags:

views:

86

answers:

6
CREATE PROCEDURE[BoardID]

 @sParent varchar(8000)

AS

Select Boardid,PONO,ImageStatus
from BoardDetail

Where 
    Boardid=1 and @sParent

Why @sParent create error how to use this variable on where ......i want to supply string as parameter and it will be set on where ......like @sParent

+1  A: 

You need to set a field to your @sParent parameter in the WHERE clause:

...

WHERE Boardid = 1 AND [fieldNameThatIsMatchingSParent] = @sParent

...

or similar.

Randolph Potter
A: 
WHERE 
    ((@Boardid is null and 1=1) 
    OR 
    (@Boardid is not null and @Boardid = SomeTbl.Boardid))

This WHERE clause allows us to have an optional SQL Parameter without having to resort to dynamically constructed SQL. Hope that helps!

diadiora
A: 

You can use @sParent in where in 2 ways (depending what you need)

where [FieldName] = @sParent

or

where [FieldName] Like @sParent
Dmitris
A: 
CREATE PROCEDURE[BoardID]
 @sParent VARCHAR(8000)
AS

SELECT Boardid, PONO, ImageStatus
FROM BoardDetail
WHERE 
     Boardid = 1 
AND  ('0' != @sParent AND [ColumnName] LIKE @sParent)
Binoj Antony
how can i use if in where like WHERE Boardid=1 if(@sParent!=0) AND [ColumnName] LIKE @sParent
Shamim
+2  A: 

It sounds like what you really want is to create a dynamic sql statement if that's what you want then try this:

Create Procedure [BoardID]
    @sParent varchar(8000)
AS

Declare @SQL VarChar(8500)

SELECT @SQL = 'Select Boardid,PONO,ImageStatus from BoardDetail Where 
    Boardid=1 and ' 
SELECT @SQL = @SQL + @sParent 

Exec ( @SQL)

GO
Mark
Talk about opening yourself up to SQL injection. =) But yes, I believe that's what the OP wants as well... just not very safe.
J. Steen
+1  A: 

If you want to pass where clause into your sp try this :

CREATE PROCEDURE[BoardID]

 @sParent varchar(8000)

AS

DECLARE @sql ntext

SET @sql = 'Select Boardid,PONO,ImageStatus from BoardDetail Where Boardid=1'

IF (@sParent <> '')
BEGIN
    SET @sql = @sql + ' and ' + @sParent
END

EXEC @sql
Canavar