Need Help writing a stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need Help writing a stored procedure

Author

Topic

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2010-09-28 : 13:23:29

Scenario
we have a table that has many columns but we are only concerned about two columns Requestid(not null) and groupno(null).the groupno column has been added to the request table very recently and it cannot be null for the requests going forward

I want to write a stored procedure which when triggered checks a particular requests groupno column and returns 1 if it is not null
and zero when ithe msgn column is null.

using this procedure we will be displaying the user an old request form if there is no groupno associated with the request and a new form if there is an groupno value associated to the request

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-09-28 : 13:32:36

SELECT Case WHEN groupno IS NULL Then 0 ELSE 1 END
FROM yourTable
WHERE...

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2010-09-28 : 13:52:35

Iam unable to get the syntax right.can you please validate whehte the below is correct

Create Procedure viewform
(
@requestid varchar(10),
@msgn varchar (10)
)
AS begin
select case
WHEN groupno IS NULL Then 0
ELSE 1
END
FROM request
where requestid = @request

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-09-28 : 14:01:09

[code]Create Procedure viewform
@requestid varchar(10),
@msgn varchar (10)
AS

select case WHEN groupno IS NULL Then 0 ELSE 1 END
FROM request
where requestid = @requestid
[/code] don't need @msgn in this sample either

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2010-09-28 : 15:02:38

Sorry.the stored procedure needs to be altered in such a way that

I should only pass 1 parameter which is the requestid and I should get the result as '1' or '0'

example

Exec viewform requestid

and not exec view form requestid,msgn

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-28 : 15:16:44

So remove @msgn from his example.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2010-09-28 : 15:20:55

perfect.
now how do I pass the value of the procedure to a variable

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-09-28 : 15:25:06

see here:
http://www.sqlteam.com/article/stored-procedures-returning-data

No, you're never too old to Yak'n'Roll if you're too young to die.

kevlangdo
Starting Member

5 Posts

Posted - 2010-09-29 : 08:07:01

To pass the value of the SP to a variable

in the SP

@SomeVar int output

Set @SomeVar = (select case WHEN groupno IS NULL Then 0 ELSE 1 END
FROM request
where requestid = @requestid)

Select @SomeVar

in the code (if something like VB.net pr C#) where you are calling the SP

If you are using ADO.Net
...
OutputParam = DataAdapter.InsertCommand.Parameters.Add("@SomeVar", SqlDbType.Int, 0, "groupno");
OutputParam.Direction = ParameterDirection.Output;

YourVariable = OutputParam.value;

If you are calling from another SP

declare @somevar int

Exec dbo.test @somevar

Kevin Languedoc
Sr BI Developer
www.kcodebook.com/wp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources