Why am I getting an error when passing a parameter to stored procedure?

Question

I've created a stored procedure to split IP address into four different columns.

ALTER PROCEDURE ipaddress_split
(
    @str as varchar(max)
)
AS 
BEGIN
    SET NOCOUNT ON

    DECLARE @query as varchar(max)  
    SET  @query = 'SELECT   parsename('+@str+',4) as A
                        ,   parsename('+@str+',3) as B
                        ,   parsename('+@str+',2) as C
                        ,   parsename('+@str+',1) as D';
    EXEC (@query)
    SET NOCOUNT off
END

However, I'm getting error when I pass a parameter.

EXEC ipaddress_split @str='191.168.1.1'

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.1'.

I tried all the below mentioned combinations but with no success.

exec ipaddress_split '191.168.1.1'
exec ipaddress_split 191.168.1.1
exec ipaddress_split ('191.168.1.1')

What is the proper way to pass a parameter to stored procedure?

Is there any particular reason to use dynamic sql, especially in this [horrible form](http://stackoverflow.com/search?q=sql+injection)? — GSerg, Apr 30 '12 at 12:29

score 6 · Accepted Answer · 2012-04-30T12:37:35.063

You don't have to dynamically generate the query to split the IP address input. You can do something like this.

Stored procedure script:

ALTER PROCEDURE ipaddress_split
(
    @str AS VARCHAR(max)
)
AS  
BEGIN

SET NOCOUNT ON

SELECT  PARSENAME(@str, 4) AS A
    ,   PARSENAME(@str, 2) AS B
    ,   PARSENAME(@str, 3) AS C
    ,   PARSENAME(@str, 1) AS D

SET NOCOUNT OFF

END;

Execution statement:

EXEC ipaddress_split @str = '191.168.1.1';

Output:

A   B   C   D
--- --- --- ---
191 1   168 1

Reason for your issue:

Your dynamic query evaluates to the following format. It is not treating the input to the PARSENAME function as a string.

SELECT parsename(191.168.1.1,4) as A,parsename(191.168.1.1,3) 
as B,parsename(191.168.1.1,2) as C,parsename(191.168.1.1,1) as D

You have to change the set query statement to like this by including two additional single quotes for each single quote to be displayed on the query.

set @query = 'SELECT parsename('''+@str+''',4) as A,parsename('''+@str+''',3) 
as B,parsename('''+@str+''',2) as C,parsename('''+@str+''',1) as D';

Your dynamic query will then become like this

SELECT parsename('191.168.1.1',4) as A,parsename('191.168.1.1',3) as
B,parsename('191.168.1.1',2) as C,parsename('191.168.1.1',1) as D

I wouldn't recommend doing it your way anyway. That is not the correct way to do.

Yeah it worked with your procedure but why my procedure is failing to pass the parameter. — Registered User, Apr 30 '12 at 12:33
No need to restore `nocount`. `SET`s in a stored procedures are scoped to that procedure and are reverted back automatically. — GSerg, Apr 30 '12 at 12:34
@domager [Meh](http://stackoverflow.com/questions/695568/porting-from-mysql-to-t-sql-any-inet-aton-equivalent). — GSerg, Apr 30 '12 at 12:46

score 1 · Answer 2 · answered Apr 30 '12 at 12:49

set @query = 'SELECT PARSENAME('''+@str+''',4) as A ,PARSENAME('''+@str+''',3) as B,PARSENAME('''+@str+''',2) as C,PARSENAME('''+@str+''',1) as D'
EXEC (@query)

try this. It should work.

otherwise, pass parameter like,

exec ipaddress_split @str='''191.168.1.1'''

In your case, when you execute the query it doesn't find the ip as string.

Why am I getting an error when passing a parameter to stored procedure?

2 Answers2

Reason for your issue: