CREATE PROC template help needed with simple example - SQL Server 2005 / 2008 !

Hi,

I am new to creating templates in SQL Server 2005. I want to do a simple example on CREATE PROC. Can you please provide me the code ?

BTW: Can you please explain the below syntax ?

SELECT <Columns, sysname, >

I do not have anything after the sysname comma. Can you please explain me the syntax for this ?

Thanks
milani_lucieAsked:
Who is Participating?
 
Chris LuttrellSenior Database ArchitectCommented:
Straight from BOL:
"The template scripts provided with SQL Server Management Studio contain parameters to help you customize the code. Template parameter definitions use this format <parameter_name, data_type, value>, where:
*parameter_name is the name of the parameter in the script.
*data_type is the data type of the parameter.
*value is the value that is to replace every occurrence of the parameter in the script."
That is what the 3rd part of the template syntax is for.
0
 
udaya kumar laligondlaTechnical LeadCommented:
USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.usp_GetAllEmployees', 'P' ) IS NOT NULL
    DROP PROCEDURE HumanResources.usp_GetAllEmployees;
GO
CREATE PROCEDURE HumanResources.usp_GetAllEmployees
AS
    SELECT LastName, FirstName, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment;
GO

where did you got this from
SELECT <Columns, sysname, >
0
 
milani_lucieAuthor Commented:
Creating templates (tql).

Thanks
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
udaya kumar laligondlaTechnical LeadCommented:
can you post the full query syntax for the SELECT <Columns, sysname, > , or an URL where i can refer
0
 
rob_farleyCommented:
That syntax tells you that you're selecting a list of columns, and that the list is a series of sysname types (a column name is of type sysname). Sometimes there is a style indicator after the second comma, but not in this case, so it's left blank.

So... you say:

SELECT FirstName, LastName
FROM ...

...in which 'FirstName' and 'LastName' are both valid sysname types.

Hope this helps,

Rob
0
 
milani_lucieAuthor Commented:
EXEC [dbo].[< stored_procedure_name, sysname, usp_New >]

Here usp_New is style indicator ?

Thanks
0
 
rob_farleyCommented:
It's an example of what you could use there, showing you the style of how it could work.

Rob
0
 
Chris LuttrellSenior Database ArchitectCommented:
the 3rd parameter is what will be the default in the template.  If you do the following
    SELECT <Columns, sysname, *>
    FROM <Table, sysname, sys.objects>
and run the template with <CTRL><SHIFT>M you will see that the * and sys.objects will be put in the input boxes as defaults.
0
 
milani_lucieAuthor Commented:
CGLuttrell:

I agree with you.

All,

Do we have any comment on that ?

Thanks
0
 
rob_farleyCommented:
Yes - these values get used as the defaults, but I think should only be used as an indicator of style, rather than the value you actually intend to use.
0
 
rob_farleyCommented:
Yup, and then you use the dialog box that appears to provide your own replacement values. So I prefer to put information in there that will help the user provide the right information, describing how the parameter should be used (not just a default value).

I agree, my original comment about the 'style' was misleading - it's based on my way of using templates. But I also think that BOLs comment about "the value that is to replace every occurrence" is also misleading, because the user gets prompted about what value should actually be used.

Rob
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.