Return value from sp - SQL Server Forums

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

Return value from sp

Author

Topic

bigbapu
Starting Member

2 Posts

Posted - 2014-10-12 : 07:32:10

Hello All,
I have sp with if statement. user have 4 option on form and what ever user selects it returns value from sp and I am comparing TIN No (One of the column in table.

Bellow is my sp.

if @invType = ' '
begin
SELECT CustID, CustName, Add1, Add2, City, Pincode, Phone, Mobile
FROM Customer
end
else if @invType = 'T' OR @invType = 'L' OR @invType = 'Q'
begin
SELECT CustID, CustName, Add1, Add2, City, Pincode, Phone, Mobile
FROM Customer
WHERE len(TINNo) <> 0 and state = 'Gujarat'
end
else if @invType = 'O'
begin
SELECT CustID, CustName, Add1, Add2, City, Pincode, Phone, Mobile
FROM Customer
WHERE state <> 'Gujarat'
end
else
begin
SELECT CustID, CustName, Add1, Add2, City, Pincode, Phone, Mobile
FROM Customer
WHERE len(TINNo) = 0
end

RETURN

so the problem is if TIN No is not available then it shows me error "Object reference not set to an instance of an object." because sp could not find TIN No. so I want to set default value at else statement but I dont know how to do it.

This is my first post so if you see any error in posting please forgive me.

Look forward to hear from you soon.

Thank You,

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-12 : 11:08:06

Please post your entire SP, not just the tail end.

bigbapu
Starting Member

2 Posts

Posted - 2014-10-17 : 10:45:19

Hello,
Thanks for your response please find full sp bellow.

ALTER PROCEDURE dbo.GerCustomerData

@invType char(1) output
AS
SET NOCOUNT ON

if @invType = ' '
begin
SELECT CustID, CustName, Add1, Add2, City, Pincode, Phone, Mobile
FROM Customer
end
else if @invType = 'T' OR @invType = 'L' OR @invType = 'Q'
begin
SELECT CustID, CustName, Add1, Add2, City, Pincode, Phone, Mobile
FROM Customer
WHERE len(TINNo) <> 0 and state = 'Gujarat'
end
else if @invType = 'O'
begin
SELECT CustID, CustName, Add1, Add2, City, Pincode, Phone, Mobile
FROM Customer
WHERE state <> 'Gujarat'
end
else
begin
SELECT CustID, CustName, Add1, Add2, City, Pincode, Phone, Mobile
FROM Customer
WHERE len(TINNo) = 0
end

RETURN

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-17 : 11:11:38

You need to set the output parameter, @intType, to some value. Also in your application (VB?, C#?) you need to add the output parameter when you call the SQL Stored Procedure. Actually that's probably why you're getting a null reference.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources