Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2009-04-14
11
Medium Priority
?
433 Views
Last Modified: 2012-05-06
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
Comment
Question by:milani_lucie
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 12

Expert Comment

by:udaya kumar laligondla
ID: 24144292
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
 

Author Comment

by:milani_lucie
ID: 24144299
Creating templates (tql).

Thanks
0
 
LVL 12

Expert Comment

by:udaya kumar laligondla
ID: 24144340
can you post the full query syntax for the SELECT <Columns, sysname, > , or an URL where i can refer
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 15

Expert Comment

by:rob_farley
ID: 24144352
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
 

Author Comment

by:milani_lucie
ID: 24144373
EXEC [dbo].[< stored_procedure_name, sysname, usp_New >]

Here usp_New is style indicator ?

Thanks
0
 
LVL 15

Expert Comment

by:rob_farley
ID: 24144399
It's an example of what you could use there, showing you the style of how it could work.

Rob
0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 24144412
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
 

Author Comment

by:milani_lucie
ID: 24144433
CGLuttrell:

I agree with you.

All,

Do we have any comment on that ?

Thanks
0
 
LVL 15

Expert Comment

by:rob_farley
ID: 24144459
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
 
LVL 27

Accepted Solution

by:
Chris Luttrell earned 2000 total points
ID: 24144495
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
 
LVL 15

Expert Comment

by:rob_farley
ID: 24144542
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

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

660 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question