Disable error check in Sql 2005 in Stored Proc.

Question

Disable error check in Sql 2005 in Stored Proc.

Gail-378561

SSCommitted

Points: 1602
More actions
April 26, 2007 at 11:07 am

#370621

Is there a way to disable error checking when creating a Stored Procedure in SQL 2005 or when a particular SP is run?
I keep getting
"Operand type clash: varbinary(max) is incompatible with float"
My SP exec another SP that changes the column type of the column from VarBin to VarChar. Then the SP i am trying to create then imports data to a table. The Stored Procedure doesn't realize that The column type will change and just wants to throw the error and not let me create it.
When I execute the syntax manually it works fine because then Sql realizes that the column type has been changed from varbin to varchar.
So is there a way to either turn off error checking within a particular stored procedure or in SQL all together. Prefer for it to be withing the individual SP.
I realize what I am doing may not be the best practice, but it is working out the best to do it this way for my situation.
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Omri Bahat-433962 SSC Eights! Points: 901 More actions · Answer 1

The only way I am familiar with is to put your SQL statement inside an EXEC() or sp_executesql statement. There is no direct way of telling SQL server to not validate the content and consistency of the stored proc's content (it can be done when doing cross server operations using lazy schema, but not when working in the same server).

Gail-378561 SSCommitted Points: 1602 More actions · Answer 2

I am not sure I know what you mean

Basically right now I have something like this

create stored procedure [mySproc]

As

--change varbinary column to Nvarchar using stored proc

Exec [SPtoConverttoNVarchar]

--Insert data into new Nvarchar Column

Insert into mytable (NewColumn) Select data from othertable

--After insert is complete change varchar column back to varbinary

Exec [SPtoConverttoVarBin]

Omri Bahat-433962 SSC Eights! Points: 901 More actions · Answer 3

Can you please post your code here and highlight the part in the code that causes the error? I can then show you explicitly what I mean by the above and how to work around the problem.

jezemine SSCrazy Eights Points: 8059 More actions · Answer 4

wait a sec, did I understand this right? you are changing the datatype of a table column from within a proc?

if so then you should rethink your design. the schema of a database should not be changing at runtime. tables are the most fundamental building block of a database. you are building on a very shaky foundation if your tables schemas are changing as a result of normal operations. I guarantee such a design will cause you endless headaches down the road.

---------------------------------------
elsasoft.org