October 16, 2007 at 9:45 am
How to distinguish if a parameter has been passed?
For example a sp that feeds a table:
CREATE PROCEDURE dbo.spTest(@par1 int, @par2 int = null)
AS
INSERT INTO dbo.tblTest(field1, field2) SELECT @par1, @par1
GO
I need to be free to call this sp for:
a) feed tblTest with field2 null
b) feed tblTest with field2=some int
c) feed tblTest with field2=column default
The unique solution that i've found is terrible (think it for many parameters.....):
CREATE PROCEDURE dbo.spTest(@par1 int, @par2 int)
AS
IF -1=@par2
INSERT INTO dbo.tblTest(field1, field2) SELECT @par1
ELSE
INSERT INTO dbo.tblTest(field1, field2) SELECT @par1, @par2
GO
so I pass @par2=-1 if i wont to insert the column default
(moreover @par2=-1 cannot be used)
Otherwise i think to use the field2 column default as default for the parameter @par2, but it's not useful of course
Any ideas? Thanks in advance
Roberto
October 16, 2007 at 11:55 am
Roberto, I am unclear on what you are trying to do. How are you calling the procedure? Your procedure selects, but not from another table. Are you manually entering the values for @par1 and @par2? And if so, why don't you just use an insert statement, and set the default value of field2 to whatever you want it to be?
Greg
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 16, 2007 at 12:13 pm
Hi Greg
My application inserts in that table from many places
I need to have one single 'piece of code' destined to provide inserts into the destination table.
Moreover I need to check and sometimes to modify some fields before inserting them
That's the reason
October 16, 2007 at 12:57 pm
Still not much to go on. Maybe you could post your table structure and some sample data to work with. Are you tracking which object the data came from in the destination table? What will cause the procedure to be called?
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply