query vs stored procedure

[wes987654]

I have a query that runs perfectly and it uses a function fn_split. It works just fine but when I put this query in a procedure it no longer works and tells me that fn_split is undefined?... Any ideas? I would also like to know what the ; infront of the with is for if someone would enlighten me. THANKS!

working query:

Declare 
@OrderList varchar(50)
set @OrderList = replace('SBCORD10949415, SBCORD10953377',' ','')

;with cte as (select * from fn_split(@OrderList,','))

select  T.GPItemNumber, cte.*, case when T.OrderNumber IS NULL then 0 else 1 end as hasItem from cte LEFT JOIN UserTemplate T on cte.cValue = T.OrderNumber

failing SP:

ALTER  PROCEDURE [dbo].[procGetItemnumbers]
@OrderList varchar(50)
AS
    SET NOCOUNT ON;

;with cte as (select * from fn_Split(@OrderList,','))
select  T.GPItemNumber, cte.*, case when T.OrderNumber IS NULL then 0 else 1 end as hasItem from cte LEFT JOIN UserTemplate T on cte.cValue = T.OrderNumber

 

[markros]

Use dbo.fn_Split instead of just fn_Split. When you're using UDFs, it's always a good idea to add a schema to avoid confusion.

The ; in front of the with is required by CTE definition. The last statement before cre must end with the ;

BTW, in newer versions of SQL Server ; at the end of each statement will be required, so you may try to start a habit ending each statement with a ;
(Although I myself find it difficult to change the habit).

PluralSight Learning Library