What to pass from Code >> PROC? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Development Tools

ASP.NET

What to pass from Code >> PROC?

Author

Topic

MorningZ
Starting Member

44 Posts

Posted - 2004-04-01 : 13:39:53

I have a PROC with "default" values, a la:

CREATE PROCEDURE dbo.sp_InsertFromWebService
         @first varchar(35) = NULL, 
         @blockletter bit = 0, 
         @badaddr bit = 0, 
         etc etc
AS

INSERT INTO customer ... blah blah blah

GO

my problem is this:
This PROC is used for a lot a "variations" of a form, but all forms have one major thing in common: they all have questions that ARE for sure in this "customer" table

So these forms call one common WebService that i wrote and i am wondering that for instance, "blockletter" isn't on a variation, what do i need to pass in so that the default value (in this case "0") gets inserted? I've tried DBNull.Value and also Nothing, but they put (kinda as expected, "NULL").... if i just leave out setting the parameter, i get the the SPROC was "expecting parameter blockletter"

hopefully that makes sense

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-04-03 : 21:14:12

Have a look at ISNULL() function in BOL

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-04-04 : 00:20:11

I haven't seen this documented anywhere, but all you have to do is add the parameter to the Command Object, but don't assign a value to it. This way it will pick up the default value assigned to it in the procedure. If you leave out the parameter, you will get a "Parameter expected" exception. If you add the parameter, but assign it to NULL, that's what gets passed to the proc: a NULL value. Just add the parameter, but don't assign any value to it. Sample code:


	SqlCommand cmd = new SqlCommand("up_foo", conn);
	cmd.CommandType = CommandType.StoredProcedure;
	
	SqlParameter prm1, prm2;
	prm1 = cmd.Parameters.Add("@OrderId", SqlDbType.Int);
	//prm1.Value = 10255;
	
	prm2 = cmd.Parameters.Add("@CustomerId", SqlDbType.Int);
	prm2.Value = 1;
	
	SqlDataReader rdr;
	rdr = cmd.ExecuteReader();

In the proc above, I have a default for OrderID, so even though I have initialized the parameter, I've commented out the line assigning a value to it, so the proc uses the default OrderID.

OS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources