question

Fran.otero avatar image
Fran.otero asked

Avoid implicit conversion in procedure params

Is there any option for avoid implicit conversion? The code is self explainatory. Thanks in advance, CREATE PROCEDURE PrintScreen @TextParameter as varchar(2000) AS BEGIN PRINT @TextParameter END GO EXEC PrintScreen @TextParameter=Hello --I want this sentence fail go drop procedure PrintScreen
sqlstored-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
Fatherjack avatar image
Fatherjack answered
An implicit conversion takes place when you put SQL Server in a position where it has to compare two different data types. You can avoid this by making sure that the data types it has to deal with are the same. so, make sure you provide a value of varchar(200) type to a procedure that is expecting one. If you data value might not be this type already then use CAST or CONVERT to change the data type of the value: Declare @ParamChar char(200) Declare @ParamVarChar(200) SET @ParamVarChar = CAST(@ParamChar as varchar(200)) EXEC PrintScreen @ParamVarChar I'm not sure what you mean by wanting the code to fail? If you want to avoid executing the procedure because the datatype is wrong then you need to test the parameter for its datatype either before it is passed to the EXEC command or within the procedure. When you find it is the wrong datatype then you simply need to code the correct path so that the application (and by inference the user too) knows that the code has not been executed due to the data type difference, otherwise you will have a code black hole where it will silently fail for no obvious reason. You can check the datatype of a value like this DECLARE @w AS INT SET @w = 3 SELECT SQL_VARIANT_PROPERTY(@w, 'BaseType') AS [type] go DECLARE @w AS VARCHAR(20) SET @w = 3 SELECT SQL_VARIANT_PROPERTY(@w, 'BaseType') AS [type]
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.

what fran means i think, is that the procedure still is being executet even though the provided parameter [Hello] is not in single quote marks.
1 Like 1 ·
yes, emil87b is right. This is not a problema about implicit conversions, I thing that is about compiler. If I write "select anythinghwithoutmeaning" SQL mark it as error and the instruction won't be executing. I want the same behaviour with my sample. Thanks for your responses,
0 Likes 0 ·
This is a common problem in other languages. The fix is always to make sure that the compiler, parser, or interpreter has no options for data conversion.
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.