CODEWITHSUNDEEP
sqlsql-serverfunctionstored-procedures
Matt
Matt

Reputation: 15071

SQL Converting a Function to a Stored Procedure

I have a function that has 3 parameters that i want to put into SSRS using BIDS.

I need this function to be converted to a stored procedure to do this but i cannot get this to work.

Inside the Select statement i have deleted it all in the example below as it is about 3000 lines of code with lots of individual selects and sub queries.

The create script for the function is below.

USE [SERVER1]
GO

/****** Object:  UserDefinedFunction [dbo].[NewFunction]    Script Date: 04/10/2014 11:49:21 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[NewFunction]
(
@start datetime, 
@end datetime, 
@name int 
)

RETURNS TABLE AS RETURN (

SELECT bla, bla, bla LOTS OF SELECTS AND SUB QUERYS IN HERE

FROM    t1
LEFT OUTER JOIN t2 ON t1.f1 = t2.f1 
LEFT OUTER JOIN t3 ON t1.f1 = t3.f1


WHERE f1 IN (@name)
)

GO

Upvotes: 0

Views: 78

Answers (1)

Szymon
Szymon

Reputation: 43023

The syntax for stored procedure is:

CREATE PROCEDURE [dbo].[NewProcedure]
(
@start datetime, 
@end datetime, 
@name int 
)
AS

SELECT bla, bla, bla LOTS OF SELECTS AND SUB QUERYS IN HERE

FROM    t1
LEFT OUTER JOIN t2 ON t1.f1 = t2.f1 
LEFT OUTER JOIN t3 ON t1.f1 = t3.f1

WHERE f1 IN (@name)

Upvotes: 3

Related Questions