functions within sp_executesql

[cmsbuffet]

If I execute sp_executesql, as one procedure instead of many functions, like formulaA and formulaB, will I be able to create result like this

Run

formulaA(a, b)

to produce a result

1, 2, 3

Run

formulaB(a, b)

to produce a result

 1, 2, 3

From the sp_executesql? Thanks.

 

[AlexCuse]

Just because you can, doesn't mean that you should.

Except in pretty extreme cases, its' typically best to avoid using sp_executesql. Can you please better define your situation?

[small]----signature below----[/small]
The author of the monograph, a native of Schenectady, New York, was said by some to have had the highest I.Q. of all the war criminals who were made to face a death by hanging. So it goes.

My Crummy Web Page

 

[cmsbuffet]

I was asked to put all the code (in a thread "one procedure") into one procedure, if it not possible, I will state it to the person who asked me to put all this code into one procedure. Is it possible?

 

[cmsbuffet]

Specifically, the code in the thread "one procedure" is the following

If Exists(Select * From Information_Schema.Tables Where Table_Name = 'ZipCodes' And Table_Type = 'Base Table')
    Drop Table ZipCodes

CREATE TABLE ZipCodes(
    [Country] [varchar](2) NULL,
    [ZipCode] [varchar](5) NULL,
    [City] [varchar](200) NULL,
    [state] [varchar](50) NULL,
    [StateAbbreviation] [varchar](2) NULL,
    [County] [varchar](50) NULL,
    [Unused1] [varchar](5) NULL,
    [Unused2] [varchar](1) NULL,
    [Latitude] [Decimal](8,5) NULL,
    [Longitude] [Decimal](8,5) NULL,
    [Unused3] [varchar](1) NULL
)

DECLARE @bulk_cmd varchar(1000)
SET @bulk_cmd = 'BULK INSERT ZipCodes
FROM ''C:\YourFolder\CA.txt''
WITH (FIELDTERMINATOR=''\t'', ROWTERMINATOR = '''+CHAR(10)+''')'
EXEC(@bulk_cmd)

SET @bulk_cmd = 'BULK INSERT ZipCodes
FROM ''C:\YourFolder\US.txt''
WITH (FIELDTERMINATOR=''\t'', ROWTERMINATOR = '''+CHAR(10)+''')'
EXEC(@bulk_cmd)

Alter Table ZipCodes Drop Column Unused1, Unused2, Unused3

CREATE CLUSTERED INDEX IX_ZipCodes_Zip ON dbo.ZipCodes(ZipCode)
GO
CREATE NONCLUSTERED INDEX IX_ZipCodes_Longitude_Latitude ON dbo.ZipCodes(Longitude,Latitude)
GO
GO
Select * From ZipCodes

Create Function dbo.LatitudePlusDistance(@StartLatitude Float, @Distance Float) Returns Float
As
Begin
    Return (Select @StartLatitude + Sqrt(@Distance * @Distance / 4766.8999155991))
End

Create Function dbo.LongitudePlusDistance
    (@StartLongitude Float,
    @StartLatitude Float,
    @Distance Float)
Returns Float
AS
Begin
    Return (Select @StartLongitude + Sqrt(@Distance * @Distance / (4784.39411916406 * Cos(2 * @StartLatitude / 114.591559026165) * Cos(2 * @StartLatitude / 114.591559026165))))
End

Create Function dbo.CalculateDistance(@Longitude1 Float, @Latitude1 Float, @Longitude2 Float, @Latitude2 Float)
Returns Float
AS
Begin
    Return Sqrt(Power(@Longitude2 - @Longitude1, 2) * Power(Cos((@Latitude1 + @Latitude2) / 114.591559026164646), 2) * 12391523883.7963953387686623629457364 + Power(@Latitude2 - @Latitude1,2) * 12346214104.563361) / 1609.344
End

use zipcode
go
Create Procedure GetZipcodesForDistance(
     @OriginalZipCode VarChar(5),
     @Distance Float)
AS
SET NOCOUNT ON

-- Declare some variables that we will need.
Declare @Longitude Float,
        @Latitude Float,
        @MinLongitude Float,
        @MaxLongitude Float,
        @MinLatitude Float,
        @MaxLatitude Float

-- Get the lat/long for the given zip
Select @Longitude = Longitude,
       @Latitude = Latitude
From   zipcodes
Where  Zipcode = @OriginalZipCode

-- Calculate the Max Lat/Long
Select @MaxLongitude = dbo.LongitudePlusDistance(@Longitude, @Latitude, @Distance),
       @MaxLatitude = dbo.LatitudePlusDistance(@Latitude, @Distance)

-- Calculate the min lat/long
Select @MinLatitude = 2 * @Latitude - @MaxLatitude,
       @MinLongitude = 2 * @Longitude - @MaxLongitude

-- The query to return all zips within a certain distance
Select *
From   zipcodes
Where  Longitude Between @MinLongitude And @MaxLongitude
       And Latitude Between @MinLatitude And @MaxLatitude
       And dbo.CalculateDistance(@Latitude, @Longitude,  Latitude, Longitude) <= @Distance

CREATE PROCEDURE cst_Find_Duplicates_In_Table(
        @Table    VARCHAR(25),
        @Field    VARCHAR(100))

AS
--DECLARE @Table AS VARCHAR(25)
--DECLARE @Field AS VARCHAR(100)

--SET @Table = 'Table'
--SET @Field = 'Field' --Name of Field that will determine if you have a duplicate
DECLARE @Sql AS VARCHAR(8000)

SET @Sql = 'SELECT ' + @Field + ', COUNT(' + @Field + ') AS NumCount ' +
            ' FROM ' + @Table +
            ' GROUP BY ' + @Field +
            ' HAVING (COUNT(' + @Field + ') > 1) ' +
            ' ORDER BY ' + @Field + ';'

EXEC(@Sql);

 

[cmsbuffet]

What are the benefits of sp_executesql and what are cons?

 

[AlexCuse]

The benefits are few, the cons are many. It does beat EXEC(@sql) I suppose, because you can parameterize your query if need be.

Have you tried issuing the command you have there via sp_executesql? I don't see why it wouldn't work, though in a situation like that I think I would script each object to a separate file and load/execute scripts as needed. This would allow you to store a configuration somewhere housing the execution order and what not.

You might want to put begin/ends on your procs just to avoid any issues (just because it executes, doesn't necessarily mean that it created all the objects the way you intended - best to be explicit)

create proc FooProc (@bar int)
as

[b]begin[/b]

--procedure logic here

[/b]end[/b]

Hope this helps,

Alex



[small]----signature below----[/small]
The author of the monograph, a native of Schenectady, New York, was said by some to have had the highest I.Q. of all the war criminals who were made to face a death by hanging. So it goes.

My Crummy Web Page

 

[ESquared]

sp_executesql only lets you execute a single batch at a time.

If you have script you're running that SQL Server demands to be the first statement in a batch, then that script must be submitted on its own.

The reason for this is that GO is actually a client-side instruction and is usually never submitted to the server (Management Studio or Query Analyzer submit each batch one at a time). If you do put "GO" on its own line in script submitted to sp_executesql, you may not get an error, but this is because the server is ignoring GO rather than seeing it as a batch separator.

This works, but it's not really two batches. The GO is just ignored:

DECLARE @SQL nvarchar(4000)
SET @SQL = N'SELECT @@Servername' + char(13) + char(10) + 'GO' + char(13) + char(10) + 'SELECT User_ID()'
PRINT @SQL
EXEC sp_executesql @SQL

If you try to run script that requires its own batch, you'll get an error:

DECLARE @SQL nvarchar(4000)
SET @SQL = N'SELECT @@Servername' + char(13) + char(10) + 'GO' + char(13) + char(10) + 'CREATE PROCEDURE TempProc AS SELECT 1' + char(13) + char(10) + 'GO' + char(13) + char(10) + 'SELECT User_ID()'
PRINT @SQL
EXEC sp_executesql @SQL

SQL 2000
[red][tt]Msg 111, Level 15, State 1, Line 3
'CREATE PROCEDURE' must be the first statement in a query batch.[/tt][/red]

SQL 2005
[red][tt]Msg 111, Level 15, State 1, Procedure TempProc, Line 5
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.[/tt][/red]