SQL Server Performance

SQL 2005 Temp Parameters

Discussion in 'SQL Server 2005 General Developer Questions' started by Toni, May 2, 2006.

  1. Toni New Member

    I have written some functions and want to test them but as soon as I execute the function I get the following;

    'Msg 216, Level 16, State 1, Line 1

    Parameters were not supplied for the function 'dbo.fnWTRCenPrtDivData'.'

    I understand that I need to pass the parameters into the function in order to run it, but When I press Ctrl+Sht+M I get the Specify Value for Templete Parameters box come up with Parameter, Type and Value, but the values are blank and there is no option to add the temprary params that I wish to test with.

    Anybody shed some light on this?



    Toni Chaffin
    aka Toni
  2. Toni New Member

    OK, I have found that the CTRL+SFT+M quick key only works for setting default values when you are creating a new function based on the inbuilt template within SQL.

    The problem I now have is how do I pass in parameters into the following function so that I may test it;


    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER FUNCTION [dbo].[fnWTRCenPrtDivData]
    (@chr_div_mgr char(2), @vch_portfolio_no tinyint)
    RETURNS @WeeklyTerrierRSPII TABLE
    (pormgr varchar(50), divmgr varchar(50),siteref Varchar(3), div char(2), propcat nvarchar(4))
    AS
    BEGIN
    INSERT @WeeklyTerrierRSPII
    SELECT src_portfolio_mgr.portfolio_mgr AS pormgr, src_div_mgr.div_mgr AS divmgr,
    src_centre_list.Site_Ref AS siteref, src_div_mgr.division AS div, src_centre_list.propcat
    FROM src_centre_list INNER JOIN
    src_div_mgr ON src_centre_list.Division = src_div_mgr.division INNER JOIN
    src_portfolio_mgr ON src_centre_list.Portfolio_no = src_portfolio_mgr.portfolio_no
    RETURN
    END

    Thanks in advance of your help.


    Toni Chaffin
    aka Toni

Share This Page