In storprocedure how to set variable on where ?

Question

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

Answer 1

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

...

WHERE Boardid = 1 AND [fieldNameThatIsMatchingSParent] = @sParent

...

or similar.

Answer 2

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!

Answer 3

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

where [FieldName] = @sParent

or

where [FieldName] Like @sParent

Answer 4

CREATE PROCEDURE[BoardID]
 @sParent VARCHAR(8000)
AS

SELECT Boardid, PONO, ImageStatus
FROM BoardDetail
WHERE 
     Boardid = 1 
AND  ('0' != @sParent AND [ColumnName] LIKE @sParent)

Answer 5

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

Answer 6

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