String Parser SQL Function
Guys
The post I am making now, can be one from a very old school of thought, but is a recurring problem, which the developers face, day in and day out, as they write SQL scripts.
It is about parsing a value, which contains a single quote, before passing it into the SQL queries. Many of us write various pass-through SQL scripts in our customization, and also various SQL views and stored procedures.
One way to overcome the string parsing error is to write a custom function as explained below and call it in your SQL scripts to parse strings so that they are corrected of the single quotes error.
CREATE FUNCTION StringParser ( @strUnParsed VARCHAR(20) ) RETURNS VARCHAR(20) AS BEGIN DECLARE @strParsed VARCHAR(20) SET @strParsed = REPLACE(@strUnParsed, CHAR(39), CHAR(39) + CHAR(39)) RETURN ( @strParsed ) END
Usage:
This is the way you can use this function on your SQL scripts.
SELECT * FROM RM00101 WHERE CUSTNAME LIKE ” + dbo.StringParser (@CUSTNAME) + ‘%’
This will void the @CUSTNAME parameter which is passed into the query to be corrected of any single quotes present in the name, which could result in a query parsing error.
Until next post…
Siva
April 20, 2009
В· 
veeyeskay В· 
No Comments
Posted in: SQL Server, SQL Server 2000, SQL Server 2005, SQL Server 2008 Total Views: 338