• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 442
  • Last Modified:

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
0
milani_lucie
Asked:
milani_lucie
  • 4
  • 3
  • 2
  • +1
1 Solution
 
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
 
udaya kumar laligondlaTechnical LeadCommented:
can you post the full query syntax for the SELECT <Columns, sysname, > , or an URL where i can refer
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
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
 
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
 
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.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now