question

Katie 1 avatar image
Katie 1 asked

parameter in stored procedure

All, i have question related to the parameters in a procedure. I am stuck at a point and the problem i am facing is that.. i have a parameter supplied through a stored procedure. But, Before the stored proc performs the its operation, taking that parameter , i need to check if that value supplied as a parameter, exists in the target table or not. If it exists i should throw and error and if does exist then i need to perform the intended action in the stored proc. how do i do it . For example : if( ****@corporatecode = (select corporatecode from cim.asset))**** print 'error' else ( INSERT INTO CIM.ASSET VALUES (@id,'1',' ',@Category,@corporatecode,1,null,null,null,null,null,null,null,null,null,null,null,null,1,1,@id,1) ) Problem is at the point where i have the asterisk. I am not sure how do i check the value of the parameter against the target table.
sql-server-2008stored-proceduresparameters
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
TimothyAWiseman avatar image
TimothyAWiseman answered
I think what you would want would look roughly like if exists (select * from cim.asset where corporatecode = @corpratecode) begin Print 'Error' Return end else insert into cim asset values (@id,'1',' ',@Category,@corporatecode,1,null,null,null,null,null,null,null,null,null,null,null,null,1,1,@id,1) As a slightly off topic side note, I recommend explicitly listing the columns in your insert statements even where you do not technically have to. This often makes it easier for people unfamiliar with the code to read it and can provide some protection against schema changes.
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Timothy makes a very good recommendation about listing the columns in the insert statements. In SSMS there is a very nice shortcut for spelling out a comma-delimited list of all columns. You can expand the table name on object explorer, grab the **folder** titled **Columns** and drag it to the editor window. This will spell out all columns of the table delimited by comma and by the way will not spell out the comma after the last column, so whatever is spelled out is ready to be used.
1 Like 1 ·
@TimothyAWiseman I hope you don't mind, I added the begin / end to the if block because it has more than one statement in it.
0 Likes 0 ·
Thanks for catching that! I wrote it off the cuff without testing. It didn't help that I have been working in Python all day.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.