Create Procedure in an IF block?

I am writing some code generation stuff and I am trying to get a script
like this to work:
IF (something)
BEGIN
CREATE PROCEDURE Whatever
AS
SELECT 1 as one
END
But it complains about this, so I am guessing that I can't put the
create prodcedure in an IF block.
Does anyone know of a work around for this?It's generally not a good idea to dynamically create stored procedures;
why are you trying to do that? Perhpas there's a better way to solve
the problem you're trying to do.
Stu
cmay wrote:
> I am writing some code generation stuff and I am trying to get a script
> like this to work:
> IF (something)
> BEGIN
> CREATE PROCEDURE Whatever
> AS
> SELECT 1 as one
> END
>
> But it complains about this, so I am guessing that I can't put the
> create prodcedure in an IF block.
> Does anyone know of a work around for this?|||cmay wrote:
> But it complains about this, so I am guessing that I can't put the
> create prodcedure in an IF block.
> Does anyone know of a work around for this?
If your procedure is not too complex to declare in a string, you could
create a variable that includes the CREATE PROCEDURE command and then
execute it with sp_executesql:
IF (1=1)
BEGIN
DECLARE @.sql nvarchar(1000)
SET @.sql = 'CREATE PROCEDURE Whatever
AS
SELECT 1 as one'
EXEC sp_executesql @.sql
END|||Stu wrote:
> It's generally not a good idea to dynamically create stored procedures;
> why are you trying to do that? Perhpas there's a better way to solve
> the problem you're trying to do.
> Stu
> cmay wrote:
You can use dynamic sql
IF (something)
BEGIN
exec(' CREATE PROCEDURE Whatever
AS
SELECT 1 as one')
END
But procedures are generally permenent object and why are you
interested to create them on the fly?
Regards
Amish shah|||cmay (cmay@.walshgroup.com) writes:
> I am writing some code generation stuff and I am trying to get a script
> like this to work:
> IF (something)
> BEGIN
> CREATE PROCEDURE Whatever
> AS
> SELECT 1 as one
> END
>
> But it complains about this, so I am guessing that I can't put the
> create prodcedure in an IF block.
> Does anyone know of a work around for this?
What is the real purpose of this? Using T-SQL to generate code sounds
utterly painful to me. As pointed out in another post, you would have to
use dynamic SQL, but only do this if you like to hurt yourself.
If the purpose is simply to write an installation script, I recommend that
you write the installation script in a client language: Perl, VB, VBscript
or whatever.
For more information on dynamic SQL, see
http://www.sommarskog.se/dynamic_sql.html.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I am using a Code Generation program that creates a script for the
necessary stored procedures.
I guess I could put them in a big string, but I would have to make sure
I escaped all my single quotes.
Erland Sommarskog wrote:
> cmay (cmay@.walshgroup.com) writes:
> What is the real purpose of this? Using T-SQL to generate code sounds
> utterly painful to me. As pointed out in another post, you would have to
> use dynamic SQL, but only do this if you like to hurt yourself.
> If the purpose is simply to write an installation script, I recommend that
> you write the installation script in a client language: Perl, VB, VBscript
> or whatever.
> For more information on dynamic SQL, see
> http://www.sommarskog.se/dynamic_sql.html.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||cmay (cmay@.walshgroup.com) writes:
> I am using a Code Generation program that creates a script for the
> necessary stored procedures.
> I guess I could put them in a big string, but I would have to make sure
> I escaped all my single quotes.
Ah, if you are using some program to generate the input script, putting
the CREATE PROCEDURE in dynamic SQL is a fair game. Of course you need
to double all the single quotes, and if the procedure itself employs
dynamic SQL, the result can be about unreadable. But as long as the result
is not meant to be read - who cares?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Create Procedure in an IF block?

I am writing some code generation stuff and I am trying to get a script
like this to work:

IF (something)
BEGIN
CREATE PROCEDURE Whatever
AS
SELECT 1 as one
END

But it complains about this, so I am guessing that I can't put the
create prodcedure in an IF block.

Does anyone know of a work around for this?It's generally not a good idea to dynamically create stored procedures;
why are you trying to do that? Perhpas there's a better way to solve
the problem you're trying to do.

Stu

cmay wrote:
> I am writing some code generation stuff and I am trying to get a script
> like this to work:
> IF (something)
> BEGIN
> CREATE PROCEDURE Whatever
> AS
> SELECT 1 as one
> END
>
> But it complains about this, so I am guessing that I can't put the
> create prodcedure in an IF block.
> Does anyone know of a work around for this?|||cmay wrote:
> But it complains about this, so I am guessing that I can't put the
> create prodcedure in an IF block.
> Does anyone know of a work around for this?

If your procedure is not too complex to declare in a string, you could
create a variable that includes the CREATE PROCEDURE command and then
execute it with sp_executesql:

IF (1=1)
BEGIN
DECLARE @.sql nvarchar(1000)
SET @.sql = 'CREATE PROCEDURE Whatever
AS
SELECT 1 as one'
EXEC sp_executesql @.sql
END|||Stu wrote:

> It's generally not a good idea to dynamically create stored procedures;
> why are you trying to do that? Perhpas there's a better way to solve
> the problem you're trying to do.
> Stu
> cmay wrote:
> > I am writing some code generation stuff and I am trying to get a script
> > like this to work:
> > IF (something)
> > BEGIN
> > CREATE PROCEDURE Whatever
> > AS
> > SELECT 1 as one
> > END
> > But it complains about this, so I am guessing that I can't put the
> > create prodcedure in an IF block.
> > Does anyone know of a work around for this?

You can use dynamic sql
IF (something)
BEGIN
exec(' CREATE PROCEDURE Whatever
AS
SELECT 1 as one')
END

But procedures are generally permenent object and why are you
interested to create them on the fly?

Regards
Amish shah|||cmay (cmay@.walshgroup.com) writes:
> I am writing some code generation stuff and I am trying to get a script
> like this to work:
> IF (something)
> BEGIN
> CREATE PROCEDURE Whatever
> AS
> SELECT 1 as one
> END
>
> But it complains about this, so I am guessing that I can't put the
> create prodcedure in an IF block.
> Does anyone know of a work around for this?

What is the real purpose of this? Using T-SQL to generate code sounds
utterly painful to me. As pointed out in another post, you would have to
use dynamic SQL, but only do this if you like to hurt yourself.

If the purpose is simply to write an installation script, I recommend that
you write the installation script in a client language: Perl, VB, VBscript
or whatever.

For more information on dynamic SQL, see
http://www.sommarskog.se/dynamic_sql.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I am using a Code Generation program that creates a script for the
necessary stored procedures.

I guess I could put them in a big string, but I would have to make sure
I escaped all my single quotes.

Erland Sommarskog wrote:
> cmay (cmay@.walshgroup.com) writes:
> > I am writing some code generation stuff and I am trying to get a script
> > like this to work:
> > IF (something)
> > BEGIN
> > CREATE PROCEDURE Whatever
> > AS
> > SELECT 1 as one
> > END
> > But it complains about this, so I am guessing that I can't put the
> > create prodcedure in an IF block.
> > Does anyone know of a work around for this?
> What is the real purpose of this? Using T-SQL to generate code sounds
> utterly painful to me. As pointed out in another post, you would have to
> use dynamic SQL, but only do this if you like to hurt yourself.
> If the purpose is simply to write an installation script, I recommend that
> you write the installation script in a client language: Perl, VB, VBscript
> or whatever.
> For more information on dynamic SQL, see
> http://www.sommarskog.se/dynamic_sql.html.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||cmay (cmay@.walshgroup.com) writes:
> I am using a Code Generation program that creates a script for the
> necessary stored procedures.
> I guess I could put them in a big string, but I would have to make sure
> I escaped all my single quotes.

Ah, if you are using some program to generate the input script, putting
the CREATE PROCEDURE in dynamic SQL is a fair game. Of course you need
to double all the single quotes, and if the procedure itself employs
dynamic SQL, the result can be about unreadable. But as long as the result
is not meant to be read - who cares?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

create new table format

Im new / novice user to writing SQL scripting. I have two tables and table1 has two columns

uslid and groupid,

uslid1, group1

uslid1, group2

uslid1, group3

uslid3, group1

uslid3, group3 etc

the second table2 has three columns

uslid, name, and location

uslid1, john, bldg 4

uslid2, jane, accounting

uslid3, joe, mail room

. I am trying to do a inner join but not working and not sure if this will work to create new temp table.

example of what i am trying to get in new temp table on one row:

uslid, name, location, groupid1, groupid2, groupid3

uslid1, john, bldg 4, group1, group2, group3

uslid3, joe, mail room, goup1, , group3

Hope i explained this ok. Im using 2003 server sql 2000 ver 7, thank you in advance.

Apart from the join issue, it sounds like you may not have the right database design. Here's a site that will lead you through the design effort and then explain the joins.

http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=60&rl=1

Buck Woody

create new table format

Im new / novice user to writing SQL scripting. I have two tables and table1 has two columns

uslid and groupid,

uslid1, group1

uslid1, group2

uslid1, group3

uslid3, group1

uslid3, group3 etc

the second table2 has three columns

uslid, name, and location

uslid1, john, bldg 4

uslid2, jane, accounting

uslid3, joe, mail room

. I am trying to do a inner join but not working and not sure if this will work to create new temp table.

example of what i am trying to get in new temp table on one row:

uslid, name, location, groupid1, groupid2, groupid3

uslid1, john, bldg 4, group1, group2, group3

uslid3, joe, mail room, goup1, , group3

Hope i explained this ok. Im using 2003 server sql 2000 ver 7, thank you in advance.

Apart from the join issue, it sounds like you may not have the right database design. Here's a site that will lead you through the design effort and then explain the joins.

http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=60&rl=1

Buck Woody

Create element names from data in "FOR XML PATH" query?

Hi, all. I am writing a stored procedure to create an XML-formatted export from a relational database. I am succeeding for the most part with "FOR XML PATH" queries, thanks to help from these forums, but I've hit a new issue.

I have a table we'll call "facet", and here is a subset of the table's columns:

- facet_id (nvarchar(10))

- facet_type (nvarchar(3))

- facet_value (nvarchar(255))

Part of the XML schema requires that I list these facets, and the element ID is the facet ID. I need to create this:

<facet_id>facet_value</facet_id>

<facet_id>facet_value</facet_id>

...with, of course, both "facet_id" and "facet_value" populated from the database columns. This part of the extract creates subelements to the facet owners, and there is a lower level that contains subelements to some of the facets.

Is there any way to do this? The only alternative I can see is to create a table function to pivot the "facet" table into a horizontal version of itself, but this is ugly for two reasons: performance and the complications it will create when I have to create the subelements to the facets themselves.

Thanks!

I have a similar need. I have a table-valued function that I want to return XML in which the field name itself is defined by data. In my case, these are phone numbers and I want to query a table and return a list

<PrimaryPhone>444-444-4444</PrimaryPhone>
<HomePhone>555-555-5555</HomePhone>

etc., where the node name is defined in a link table.

Best I can come up with so far is something like:

SELECT
'<' + cpt.DisplayName + 'Phone>'
+ ltrim(rtrim(cp.PhoneNumber))
+ '</' + cpt.DisplayName + 'Phone>' AS "node()"
FROM Customer c
INNER JOIN CustomerPhone cp ON cp.CustomerId = c.CustomerId
INNER JOIN CustomerPhoneType cpt ON cpt.CustomerPhoneTypeId = cp.CustomerPhoneTypeId
WHERE c.CustomerId = @.customerId
FOR XML PATH(''), TYPE

But the special symbols (<, >, etc) are automatically converted to their escaped equivalents so that will not work.

Any advice?|||

cast the string expression to xml should work:

SELECT cast
( '<' + cpt.DisplayName + 'Phone>'
+ ltrim(rtrim(cp.PhoneNumber))
+ '</' + cpt.DisplayName + 'Phone>' as xml) AS "node()"
FROM Customer c
INNER JOIN CustomerPhone cp ON cp.CustomerId = c.CustomerId
INNER JOIN CustomerPhoneType cpt ON cpt.CustomerPhoneTypeId = cp.CustomerPhoneTypeId
WHERE c.CustomerId = @.customerId
FOR XML PATH(''), TYPE

create dymanic dts

i can writing dynamic dts package . but not working.

code is here :

Public goPackageOld As New Package
Public goPackage As Package2

Public Sub RunDTS()
Dim goPackage As Package2
goPackage = CType(goPackageOld, Package2)
goPackage.Name = "DTS3"
goPackage.Description = "DTS package description"
goPackage.WriteCompletionStatusToNTEventLog = False
goPackage.FailOnError = False
goPackage.PackagePriorityClass = CType(2, DTSPackagePriorityClass)
goPackage.MaxConcurrentSteps = 4
goPackage.LineageOptions = 0
goPackage.UseTransaction = True
goPackage.TransactionIsolationLevel = CType(4096, DTSIsolationLevel)
goPackage.AutoCommitTransaction = True
goPackage.RepositoryMetadataOptions = 0
goPackage.UseOLEDBServiceComponents = True
goPackage.LogToSQLServer = False
goPackage.LogServerFlags = 0
goPackage.FailPackageOnLogFailure = False
goPackage.ExplicitGlobalVariables = False
goPackage.PackageType = 0

Dim oConnProperty As OleDBProperty
'

' create package connection information

'

Dim oConnection As Connection2
'- a new connection defined below.

oConnection = CType(goPackage.Connections.New("DTSFlatFile"), Connection2)

oConnection.ConnectionProperties.Item("Data Source").Value = "C:\hede\50.txt"
oConnection.ConnectionProperties.Item("Mode").Value = 1
oConnection.ConnectionProperties.Item("Row Delimiter").Value = "||##"
oConnection.ConnectionProperties.Item("File Format").Value = 1
oConnection.ConnectionProperties.Item("Column Delimiter").Value = "|#$,"
oConnection.ConnectionProperties.Item("File Type").Value = 1
oConnection.ConnectionProperties.Item("Skip Rows").Value = 0
oConnection.ConnectionProperties.Item("First Row Column Name").Value() = True
oConnection.ConnectionProperties.Item("Max characters per delimited column").Value = 8000
oConnection.Name = "Connection 1"
oConnection.ID = 1
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "C:\hede\50.txt"
oConnection.ConnectionTimeout = 60
oConnection.UseTrustedConnection = False
oConnection.UseDSL = False
goPackage.Connections.Add(CType(oConnection, Connection))

oConnection = CType(goPackage.Connections.New("SQLOLEDB"), Connection2)
oConnection.ConnectionProperties.Item("Integrated Security").Value = "SSPI"
oConnection.ConnectionProperties.Item("Persist Security Info").Value() = True
oConnection.ConnectionProperties.Item("Initial Catalog").Value = "**"
oConnection.ConnectionProperties.Item("Data Source").Value = "(local)"
oConnection.ConnectionProperties.Item("Application Name").Value = "DTS Import/Export Wizard"
oConnection.Name = "Connection 2"
oConnection.ID = 2
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "(local)"
oConnection.UserID = "**"
oConnection.Password = "**"
oConnection.ConnectionTimeout = 60
oConnection.Catalog = "**"
oConnection.UseTrustedConnection = True
oConnection.UseDSL = False
goPackage.Connections.Add(CType(oConnection, Connection))
oConnection = Nothing

'

' create package steps information

'

Dim oStep As Step2
Dim oPrecConstraint As PrecedenceConstraint

oStep = CType(goPackage.Steps.New, Step2)
oStep.Name = "Copy Data from myTextFile to [(local)].[dbo].[111] Step"
oStep.Description = "Copy Data from myTextFile to [(local)].[dbo].[111] Step"
oStep.ExecutionStatus = CType(1, DTSStepExecStatus)
oStep.TaskName = "Copy Data from myTextFile to [(local)].[dbo].[111] Task"
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ScriptLanguage = "VBScript"
oStep.AddGlobalVariables = True
oStep.RelativePriority = CType(3, DTSStepRelativePriority)
oStep.CloseConnection = False
oStep.ExecuteInMainThread = False
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
oStep.FailPackageOnError = False
goPackage.Steps.Add(oStep)
oStep = Nothing
goPackage.SaveToSQLServer("(local)", "**", "**", DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "", "", "")
Try
goPackage.Execute()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub

not have error. try clause is running.but not correct result.

thx..

The Package.Execute method will only thow an exception if it cannot run the package at all, virtual impossible to get. It does not throw an exception if the package fails, as that is still a valid execution.

To capture details of any errors that happend within the package, use the package events provider.

HOW TO: Handle Data Transformation Services Package Events in Visual C# .NET
(http://support.microsoft.com/kb/319985/en-us)

|||

ok i done.

but it didnt have error.

created package in sql server.

but double click on package have error =

Error Source: Microsoft Data Transformation Services(DTS) Package
Error Description: Task 'Copy Data from C:\hede\hede.txt to [(local)].[dbo].[111] Task' was not found.

i want to here : can i do writing Transformation Task Name = Task