creating stored procedure

drachx · October 14th, 2004, 01:45 AM

hi guys,
how can i create one stored procedure that have one parameter that will find the field (received) that is null or not null

i have the sql that works but i want is one Sp only for these
thanx

happygv · October 14th, 2004, 02:54 AM

You got to use dynamic sql then. construct the sql statement that takes the field parameter and then use EXEC function to execute that.

Code:

Create proc ProcName (@FIELDNAME varchar(100))
as
    Declare @strSql varchar(100)
    @strSql = "Select * from tablename where @FIELDNAME is NULL"
    Exec(@strSql)

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

bmains · October 14th, 2004, 08:10 AM

create procedure dbo.spGetNullOption

as

declare @IsNull int

select @IsNull = ISNULL(count(received), 0) from table1 where received is NOT NULL

'returns bit value
if (@IsNull > 0)
return 1
else
return 0

Brian