Creating Stored Procs Based On SQL Server Version

Jul 20, 2005

We have a product that we were experiencing high recompile rates do to
temp tables, we have re-written them to use table variables to reduce
recompile rate. The problem I am having is that we have some
customers still on SQL 7 where the table variables were not valid. I
would like to have one installation script that would create the
stored procedure with table variables on SQL Server 2000 and without
on SQL Server 7, I have been unsuccessful do to the limitation that
CREATE PROCEDURE statement cannot be in a script with any other
statements.

Was wondering if anyone else has run into this and found a way around
it.

View 1 Replies


ADVERTISEMENT

How To Execute Stored Procedure Differently Based On SQL Server Version

Oct 16, 2007

My product was developed for and works correctly on SQL Server 2000. However, when we upgraded to 2005, we found that certain system stored procedures were different, causing our product to break.

We can easily change our stored procedures to work in 2005, but we have a large client base, some of whom will be using each version. Our current solution is to check the version of SQL Server during installation and choose which script to use at that time in order to have an appropriate stored procedure for that version, but we are concerned about users who install our product with SQL Server 2000 and then upgrade to SQL Server 2005.

How can I make a stored procedure that will run differently depending on the version? I tried something like:


if (select charindex('2000', @@version)) > 0

begin -- SQL Server 2000


SELECT

...
FROM

...
WHERE
end
else -- SQL Server 2005

SELECT

...
FROM
...
WHERE
end

Unfortunately, the system tables I'm selecting from have different stuctures in the different versions (one example is msdb.dbo.sysjobschedules and msdb.dbo.sysschedules), and even though the code never gets into the SQL Server 2000 section on 2005, it parses the whole procedure for errors before allowing it to be saves and will not allow this.

Any thoughts?

View 3 Replies View Related

Creating Sql Strings In Stored Procs

Jul 31, 2001

Hi Guys,

I have written a piece of code in a stored procedure that builds a string called "filter$" based on fielde in a table.
How do I use this string as my where clause?

in vb I would use:
rs.open filter$,cn

I hope this makes sense and someone can help me

View 2 Replies View Related

Calling Stored Procs Based On A Code Table

Sep 8, 2006

I need to run stored procs based on a list in a code table. In other words, it reads the name of a stored proc from a table and runs it and does this for all the rows in the table. I think the ForEach loop container will do what I need and there is an ADO enumerator option but the documentation does not tell you how to use this. From what I can tell, you need to get a dataset into an SSIS variable first and then you plug the variable name into the ForEach ADO enumerator. Is that correct? If so, can someone tell me how to get a dataset into a variable?

Thanks

View 1 Replies View Related

Creating Stored Procs That Need To Continusiouly Append To A New Table (this Is To Scrub Data That Is Imported Into DB).

Jan 9, 2005

I have 1 table with a huge amount of data that I recive from someone else in a flat file format. I want to be able to filter through that data and scrub it and find out the good data and bad data from it.

I'm scrubbing the data using different stored procs that i've created and through a web interface that the user can pick which records they wish to create.

If I were to create a new table for clean records, what is the syntax to keep Appending to that table through the data that i'm obtainig via the stored procs that i've created.

Any thoughts or suggestions are greatly appriciated in advance

Thanks again in advance
RB

View 1 Replies View Related

Stored Procs: Is There A Way To Divide Up (or Namespace) Groups Of Stored Procs

Jan 15, 2008

Is there a way to namespace groups of stored procs to reduce confusion when using them?

For C# you can have ProjectName.ProjectSection.Classname when naming a class. I am just wondering if there is a way to do the same with SQL stored procs. I know Oracle has packages and the name of the package provides a namespace for all of the stored procs inside it.

View 1 Replies View Related

Creating Stored Procedures Based On Variables

Oct 5, 2006

Hi There,

I would like to know if for example i have some tables in my DataBase and i need from each table to get for instance, the Name by the ID, how can i make only one procedure which use parameters and variables so i can use this SP to get the Name By ID for all the Tables?

Thanks

View 1 Replies View Related

Corrupt Stored Procs On SQL Server 6.5

Jun 20, 2000

Hi

Recently I have experienced the following scary scenario;

Users report a problem with an app, on diagnosis it is found that a bunch of SPs in SQL Server seem to be corrupt.

That is, when the code for them is listed, the 'Create Procedure..' line and several DECLARE lines are missing. Often the SP starts off in the middle of a variable name!!

Perhaps entries in the syscomments table have become corrupted?

Has anyone seen this problem before - is it perhaps a known bug in SQL Server 6.5?

Regards,

View 1 Replies View Related

Problem With Using Stored Procs As I/p To Another Stored Procs

May 7, 2004

HI,

CREATE PROCEDURE PROC1
AS
BEGIN
SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC
FROM CUSTOMER A
INNER JOIN CUSTOMERPREFERENCE B
ON A.INTCUSTOMERID = B.INTCUSTOMERID
INNER JOIN TMPREFERENCE C
ON B.INTPREFERENCEID = C.INTPREFERENCEID
WHERE B.INTPREFERENCEID IN (6,7,2,3,12,10)
ORDER BY B.INTCUSTOMERID

END

IF I AM USING THIS PROC AS I/P TO ANOTHER PROC THEN IT GIVES NO PROBLEM AS I CAN USE ?

CREATE PROCEDURE PROC2
AS
BEGIN

CREATE TABLE #SAATHI(INTCUSTOMERID INT,CHREMAIL NVARCHAR(60),INTPREFERENCEID INT,CHRPREFERENCEDESC NVARCHAR(50))

INSERT INTO #SAATHI
EXEC PROC1


ST......1,
ST......2,


END.

BUT IF , I USE ANOTHER PROC SIMILAR TO THE FIRST ONE WHICH GIVES SLIGHTLY DIFFERENT RESULTS AND GIVES TWO SETS OF RESULTS,THEN WE HAVE A PROBLEM,HO TO SOLVE THIS :-


CREATE PROCEDURE MY_PROC
AS
BEGIN
SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC
FROM CUSTOMER A
INNER JOIN CUSTOMERPREFERENCE B
ON A.INTCUSTOMERID = B.INTCUSTOMERID
INNER JOIN TMPREFERENCE C
ON B.INTPREFERENCEID = C.INTPREFERENCEID
WHERE B.INTPREFERENCEID IN (23,12,10)
ORDER BY B.INTCUSTOMERID

END

SELECT A.INTCUSTOMERID,MAX(case when A.intpreferenceid = 23 then '1'
else '0' end) +
MAX(case when A.intpreferenceid = 12 then '1'
else '0' end) +
MAX(case when A.intpreferenceid = 10 then '1'
else '0' end) AS PREFER
FROM CUSTOMER
GROUP BY A.INTCUSTOMERID
ORDER BY A.INTCUSTOMERID
END

WHICH NOW GIVES ME TWO SETS OF DATA , BOTH REQUIRED THEN HOW TO USE ONE SET OF RESULTS AS I/P TO ANOTHER PROC AND OTHER SET OF RESULTS AS I/P TO YET ANOTHER PROC .



CREATE PROCEDURE PROC2
AS
BEGIN

CREATE TABLE #SAATHI(INTCUSTOMERID INT,CHREMAIL NVARCHAR(60),INTPREFERENCEID INT,CHRPREFERENCEDESC NVARCHAR(50))

INSERT INTO #SAATHI
EXEC MY_PROC


ST......1,
ST......2,

END.

BUT, HERE I WANT TO USE FIRST DATASET ONLY , HOW TO USE IT ?

THANKS.

View 4 Replies View Related

Working With SQL Server Temp Tables In Stored Procs

Nov 18, 2005

I am trying to create a SQL data adapter via the wizard, however, I get
the error "Invalid object name #ords" because the stored procedure uses
a temp table. Anyway around this? Thanks.

View 11 Replies View Related

Linked Server Error Handling In Stored Procs.

Jun 29, 2004

I would like to capture any errors thrown by linked server in my stored procedure, How do I do this? eg: Select statement in sp to remote server fails if login credentials were changed for some reason on remote server. @@error is not able to capture this error and stored procedure is terminated when this type of error occurs.

View 1 Replies View Related

SQL Server 2000 Table Rename Impact On Stored Procs

Oct 12, 2004

SQL Server 2000:

Question 1

If you drop / rename a table and then recreate the table with the SAME NAME, what impact does it have on stored procedures that use these tables? From a system perspective, do you have to rebuild / recompile ALL the stored procedures that use this table?

I had a discussion with someone that said that this is a good idea, since the IDs of the tables change in sysobjects and from a SQL SERVER query plan perspective, this needs to be done...

Question 2

If you Truncate a Table as part of a BEGIN TRANSACTION, what happens if an error occurs? Will it Rollback? The theory is that it won't because Truncate doesn't utilize the logs where as Delete From uses the SQL Logs?

Thanks!

View 1 Replies View Related

How To Capture SQL Server Does Not Exist Type Errors Within Stored Procs

Nov 16, 2007

Hi, I have a stored procedure running on our monitoring server to monitor the backup jobs on other sql servers running on our network. This sp loops thorugh the list of server names and connects remotely to other servers using linked servers. However, if one of the servers is down, the sp stops after raising 42000 error ("SQL Server does not exist") and fails to carry on processing the next server in the list.
Is there any way to capture this "SQL Server does not exist" error within the sp so that the sp can carry on with the processing?
I am using SQL Server 2000 SP4.
Thanks in advance for any replies.
Opal

View 9 Replies View Related

Viewing Large Numbers Of Stored Procs In SQL Server Management Studio

Jul 13, 2007

I work with a large and complex reporting system with several hundred reports: the Programmability; Stored Procedures node of the object explorer has become very difficult to navigate.



Is there any metadata that can be embedded in the stored procs that would create subfolders like the existing System Stored Procedures node in this node of the object explorer?



I suspect that the correct answers are:

Rename all your queries with a rational naming convention;
Cull the deadwood;
Assign them to categories then export them to separate databases.

Unfortunately, one of these has already been done, and the other two will break several hundred dependent processes - the recoding and retesting is neither economical nor desirable.



Still, all advice is welcome. Pitch your answers at a banking geek who does intermediate to advanced stored procedures and triggers, but isn't allowed to play with sharp things (like sys objects) - but I can probably get help from a grown-up on the sysadmin team: I have discovered that the rumours about human sacrifice are baseless, and they will perform favours in return for beer.



This is also a good time to ask: just how many stored procs and functions are you allowed in SQL Server 2005?



Nile.

View 5 Replies View Related

SQL Server 2014 :: SSAS Stored Procs (CLR) - Identify Real Data Type Of MDX Value Returned From Expression

Feb 13, 2015

I have a SSAS stored procedure with a signature:

public Set DoSomthing(Set toBeProcessed, Set measuresToWorkWith)The set measurseToWorkWith is passed as {[Measures].[Measure1], [Measures].[Measure2] ...}

with the measures being real or query-scoped calculated members.

To get the value of the measure for each tuple in the set toBeProcessed, I create an Expression for each tuple (measure) in the set measuresToWorkWith then for each tuple in toBeProcessed call expression.Calculate(tuple) which returns a MDXValue.

My problem is that in order to make the code generic I need to get the real (.NET) data type of the MDXValue. The class only has explicit conversion methods ToInt16() etc which implies that the data type is known at design time.

However, if one of the measures is a query-scoped calculation then it could return a .NET double, int, bool or string.

If the measure is real then I can look up its metadata. However, it appears that if it is a formula (scoped member) then are all bets are off?

View 0 Replies View Related

SQL Server 2012 :: Error Creating Temp Table Based On IF Logic

Nov 13, 2014

I could deploy across my environment, which is a mix of 2008R2/2012 servers, to give some information on log files. Running into a silly issue right off the bat. The table that DBCC LogInfo() conjures out of magic is different between the two. In 2012 it gained the RecoveryUnitID column. So I'm trying to write some logic to create a temp table based on which version is running. I would like to avoid a global temp table if possible. Here's what I've tried:

sp_executesql creates a table outside of the scope of my session:
DECLARE @PrVers NVARCHAR(128)
, @PrVersNum DECIMAL(10,2)
, @StageTable NVARCHAR(1024) = N''

[code]....

View 9 Replies View Related

SQL Server 2008 :: Creating Multiple Charts Based Off Of Main Chart

Jun 4, 2015

I have this main chart setup. It pulls data from a query based on the selected parameters.

I want to create multiple charts for every catagory in the result set.

On the main chart there is a catagory called "Missing".

I want a chart for this category to display when the main chart is loaded. So below the main chart it would show a chart for each of the categories on the main chart.

Basically this report returns information on a certain part number displaying its problem types. Then I want it to create other charts for each problem type displaying the problem sub-types.

View 1 Replies View Related

How To Search And List All Stored Procs In My Database. I Can Do This For Tables, But Need To Figure Out How To Do It For Stored Procedures

Apr 29, 2008

How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out?
 SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

View 1 Replies View Related

SQL Server 2012 :: Creating Running Total Based On Single (SUM) Opening Balance

Jun 7, 2015

I am creating a query that shows the consumption of stock against Manf Orders (M/O) and struggling on the last hurdle. I am having difficulties calculating a running total based on an Opening Balance. The first line returns the correct results but the following lines do not. I have tried other variants of the "Over Partition" but still no joy?

SELECT CASE WHEN ROWNUMBER > 1 THEN ''
ELSE A.Component
END AS Component ,
CASE WHEN ROWNUMBER > 1 THEN ''
ELSE A.SKU

[Code] ....

Please see attachment to view output.

View 7 Replies View Related

How To Use A Stored Procedure Inside Select Query (sql Server Version 8)

Sep 29, 2007



Hi,
Please help me in this problem...
i am new to sql server..
i am using sql server version 8...(doesnot support function with retun values..)
so i have created a procedure...
-----------procedure------------------(to find next monday after 6 months)-------------------
[code]
create proc next_Monday ( @myDate DATETIME )
as
BEGIN
set @myDate = dateadd(mm, 6, @myDate)
while datepart(dw,@myDate) <> 2
begin
set @myDate = dateadd(dd, 1, @myDate)
end
select @myDate
end
go
[/code]
--------------------------------------------------------
i can able to execute this procedure separately.... working well...
but don't know how to call it inside another query....
the following throws error....
select smaster.sname, smaster.Datex, 'xxx'=(execute next_monday smaster.Datex) from smaster
please help me... how to fix this problem...

View 16 Replies View Related

Stored Procs Are BAD!! BAD, I Tell You!!!

Feb 5, 2005

almost choked when i read the following recent post on The Daily WTF (http://thedailywtf.com/) the other day --

Logical Tiers? That Makes No Sense! (http://thedailywtf.com/ShowPost.aspx?PostID=28959)

i don't think i can do justice to how utterly stupid that stored procedure is

read the comments and have a laugh

one of the points made was that stored procedures are bad

this twigged something in my memory, so i dug around in my bookmarks, and sure enough, here's another decent discussion about stored procs --

Stored procedures are bad, m'kay? (http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx)

enjoy!

View 6 Replies View Related

.exe From Stored Procs

Jun 28, 2007

Hey guys.

I am wondering if it is possible to execute an .exe from a stored proc. I have an application that calls a stored proc and I am wanting for that stored proc to call up an app to show certain results. I have no way of doing this on the application itself as I dont have the source code. So, since I do have access to the SQL I am wondering if it could be done there.

Thanks
tibor

View 14 Replies View Related

I Need Help With Stored Procs And UDF

Apr 8, 2004

First off, this is a cross post which is also located here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=34073

Now, with the following stored procs and UDF, the Result is NULL and I cannot figure out why.

What I am doing is calling a stored procedure pass in an OUTPUT variable of type float. This stored proceudre then calls a different stored procedure passing the same OUTPUT variable of type float. The second stored procedure then calls a UDF passing in two variables to be multiplied ans this should set the OUTPUT variable to the result.

The UDF does return the correct result to the interior stored procedure, but the interior stored procedure does not pass the value back to the original stored procudure resultint in a Result value = NULL.

The code is below, just copy / past, execute and you will see exactly what I mean.

Any thoughts?


USE NORTHWIND
GO

CREATE FUNCTION RECTANGULAR_XSECTION
(@fWidth float, @fHeight float)

RETURNS float

AS
BEGIN
RETURN (@fWidth * @fHeight)
END
GO


CREATE PROCEDURE usp_shapes_GetRectangularXSection

@fResult float OUTPUT

AS

declare @fWidth float, @fHeight float
SELECT @fWidth = 108, @fHeight = 10

SELECT @fResult = [dbo].[RECTANGULAR_XSECTION](@fWidth, @fHeight)
SELECT @fResult AS CalledProcedureOkHere

GO

CREATE PROCEDURE usp_shapes_GetXSection

@fResult float OUTPUT

AS

EXECUTE usp_shapes_GetRectangularXSection @fResult
SELECT @fResult AS CallingProcedure_NULL?
GO

declare @fResult float
EXECUTE usp_shapes_GetXSection @fResult
SELECT @fResult as OutsideCallingProcedure_NULL?
GO

DROP FUNCTION RECTANGULAR_XSECTION
GO
DROP PROCEDURE usp_shapes_GetRectangularXSection
GO
DROP PROCEDURE usp_shapes_GetXSection
GO


Mike B

View 3 Replies View Related

New To Stored Procs

Nov 13, 2007

I am trying to improve my SQL and reduce the number of connections my website is making to the server.

Currently I have a stored procedure that gets all the games (and their details) for a single user which works fine which uses a WHERE userid = @userid which is an Int value.

I now need to create a new procedure which brings back the to all their "friends" (again by their userid). How is it best to do this? I originally tried to do a WHERE userID IN (@userid) but was unable to work out what variable type to use for @userid.

Is it best to do a single query in this way or is there a way to use the existing SP, loop through the collection of userids and join the result together into a single set to return? If an IN is the best route, what is the correct datatype for the variable?

View 5 Replies View Related

SQL Stored Procs

Aug 8, 2006

can anyone help me out.... i need to compare a date that was retrieved from the database and the system date then it must be coded in the stored procs of the database.. help!!!!

View 10 Replies View Related

Transactions And Stored Procs

Mar 8, 2004

All,

I'm relatively new to stored procs (not to SQL or SQL Server) and I am trying to get transactions to work within a stored proc. Here is the code:


(
--define the parameters that are needed
@userID char(32),
@userName varchar(50),
@status char(10),
@type char(10),
@password varchar(50),
@firstName varchar(100),
@lastName varchar(100),
@email varchar(200),
@domain varchar(50),
@pwdExpiry int,
@badLogin int,
@lastLogin datetime,
@full varchar(8000),
@read varchar(8000),
@noaccess varchar(8000)
)
AS
BEGIN TRAN tmp1
--First, insert the user into the system
INSERT INTO ptsUsers(UserID, UserName, Status, Type, Password, FirstName, LastName, Email, DomainName, PasswordExpiry, BadLoginAttempts, LastLoginDate)
values(@userID, @userName, @status, @type, @password, @firstName, @lastName, @email, @domain, @pwdExpiry, @badLogin, @lastLogin);

--Now, we need to add the function access edges for the user
declare @arrayValue char(32)
declare @rightID char(32)
declare @sepPos int
while patindex('%,%',@full)<>0
BEGIN
select @sepPos = patindex('%,%' , @full)
select @arrayValue = left(@full, @sepPos - 1)
-- replace the value with an empty string
select @full = stuff(@full, 1, @sepPos, '')

--create and parse the new id
declare @strID char(32),@tmpStr varchar(40)
set @tmpStr = newid();
set @strID = REPLACE(@tmpStr,'-','')

--get the access right id for full control
select @rightID = (Select AccessRightID from ptsAccessRights where Name = 'Write')
if(@rightID IS NOT NULL)
--insert the records that are full access
INSERT INTO ptsFunctionAccessEdges(FunctionAccessEdgeID, FunctionID, AccessRightID, UserID)
VALUES(@strID, @arrayValue, @rightID, @userID)
else
RAISERROR(50100,15,1)
END
COMMIT TRAN tmp1
IF @@TRANCOUNT > 0
RAISERROR(50101,15,1)
ROLLBACK TRAN tmp1


The transaction does not rollback when any errors occur at all. Any advice/help?

View 3 Replies View Related

IF Statement In Stored Procs... Help!

Apr 12, 2004

Hi all!

I am not an expert in Stored Procs. I would like to build one for a product list that would return a default value without using output parameters, if possible. Ultimately, I wouldn't be opposed to it.

It currently looks like this:


CREATE PROCEDURE ProductsByVendorNo
(
@VendorNum nvarchar(24)
)
AS

SELECT
P.ProductID AS ProductID,
P.ProductShortName AS ProductName,
P.ProductDesc AS ProductDesc,
U.UnitDesc AS Unit,
P.VendorProductNumber AS VendorNumber,
V.VendorName AS VendorName,
P.Price AS Price,
P.ImageThumb AS ImageThumb

FROM
tblProducts AS P
INNER JOIN tblUnitCodes AS U ON P.UnitCode=U.UnitCode
INNER JOIN tblVendors AS V ON P.VendorID=V.VendorID

WHERE
V.VendorsVendorNo = @VendorNum
AND P.Inactive = 0

ORDER BY
ProductName,
VendorNumber



I would like for it to return a default, constant value for the URL in the ImageThumb field, if this one is empty. I could not find good documentation of how to use IF statements for this case, i.e. to alter the return of just one field.

Suggestions?

Any input is highly appreciated.

Thanks in advance,

Mili Skikic

View 8 Replies View Related

Security On Stored Procs On Dev Db

Mar 25, 2002

I want to "deny" create, update,and delete access on the dbo stored procs that are in the database, but do not want take away dbo owner access. is this possible?

can i create a role and deny access on a particular table in msdb? or a system table in the user table. Thus preventing the developers on the box access to update any of the dbo owned sp's and have them create their own user-owned stored procs?

this is sql7, sp3, development box.

thanks,

View 1 Replies View Related

Using Stored Procs In A DTS Package

Apr 27, 2001

I am trying to set up a DTS package that selects data from one table on server A into another table on server B. I want to do a select statement that will do the following:

select store_name
from store (server A)
where date_created >= (select max(date_created) from store (server B)

I use EM 7.0 to manage all of my extracts, however, the data is moving from a Syabase (adaptive Server)onto another Syabase(adaptive server) machine.
Unfortunately, there is no functionality for a linked server connection.

I tried the following, (which doesn't error out), but is not displaying the source columns in the destination tab of the DTS package when setting up the transformations.

declare @maxdate datetime
exec serverB.dbo.sp_max_date_from_store @maxdate
select store_name
from store --(server A)
where date_created >= @store

Any help or suggestions would be greatly appreciated!
trevorb

View 1 Replies View Related

Not Normal Will Stored Procs Help

Jun 19, 2000

I will be taking over a database that has almost no pk's or relations(this is not my choice, but a vendors)
Management is looking at stored procs to improve performance, but I am wondering
if the db is in this state will there really be a gain. I am pushing for normalization
first, but if anybody has any ideas or opinions I would appreciate

View 2 Replies View Related

Stored Procs And Parameters

Sep 10, 2001

I have a stored proc that is run periodically which I execute with a DTS package. The problem is that I have to update a field for each record selected by this stored proc with a specific date. I need to prompt the operator for this date so I can use this value.

View 6 Replies View Related

Errors And Stored Procs

Jul 28, 2004

Ok, I've read somewhere(which I'm looking for again : ) that said that there are errors like DeadLock that kills the execution of a stored proc and there are other errors that do not necessarily kill the rest of the execution of the stored proc. Is that true? If so does anyone have any links I can read. What I'm seeing is a bad id in the foreign key and I think what is happening is that there was a unique constraint error on the first insert but the stored proc continued executing and used the bad id later on in the stored proc.

I do know I can use the @@error and will start using it but I need more proof to agree or not agree with my theory.

Thanks ahead of time for any information you can give me either way.

DMW

View 2 Replies View Related

Views And Stored Procs.

Aug 31, 2004

Is there any performance loss by setting up a view and creating a stored procedure to return a recordset from a view versus setting up the stored procedure to return the recordset from the tables directly?

What way should I do this?

Mike B

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved