Stored Procedures - Using Return Value From Dynamically Created SQL Statement

Jul 29, 2007

Hello,

This is my foray into Stored procedures, so I'm hoping this is a fairly basic question.

I'm writing a stored procedure, in which I dynamically create an SQL statement.
At the end of this, the SQL statement reads like:



Code SnippetSELECT COUNT(*) FROM StockLedger WHERE StockCode = 'STOCK1' AND IsOpen = 1 AND SizeCode = 'L' AND ColourCode = 'RED' AND LocationCode IS NULL AND RemainingQty > 0



Now this statement works a charm, and returns a single value. I want to assign this count to a variable, and use it further on in the stored procedure. This is where the problems start - I cant seem to do it.

If I hard code a statement, like



Code SnippetSELECT @LineCount = COUNT(*) FROM StockLedger
that works fine (although it brings back a count of all the lines).

But if I modify the dynamically created SQL Statement from earlier on to:



Code SnippetSELECT @LineCount = COUNT(*) FROM StockLedger WHERE StockCode = 'STOCK1' AND IsOpen = 1 AND SizeCode = 'L' AND ColourCode = 'RED' AND LocationCode IS NULL AND RemainingQty > 0
it doesnt work - it complains: Must declare the scalar variable "@LineCount".

Just to clarify, when I say "dynamically created an SQL statement, I mean that by a bunch of conditional statements I populate a varchar variable with the statement, and then eventually run it exec(@SQLStatementString)

So, my question would be, how do I do this? How do I make a dynamically generated SQL statement return a value to a variable?


View 3 Replies


ADVERTISEMENT

Dynamically Created Stored Procs???

Mar 8, 2004

Okay. Scenario:

We as a company have several companies that request custom reports from us. However, the custom reports that they select will generally always have the same fields (and formulas) once they make up their minds on what works for them.

From what I know, Stored Procs are usually faster at running things unless the parameters are changing too drastically that they get passed.

This being the case, it would seem like a good case for creating a stored proc per report. This would alleviate the possability that the server chooses an execution plan that works great some of the time but the rest of the time run lousy.

So these thoughts being laid out, is there a good,nice,easy, convenient way to generate/alter a stored proc, either by another stored/extended proc, or by dynamic sql going to the server?

Or if not, is there some round-about yet effective way of doing this?


Thank you in advance for any help.
-ZanderB

View 8 Replies View Related

All My Stored Procedures Are Getting Created As System Procedures!

Nov 6, 2007



Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.

For example, even this simple little guy:

CREATE PROCEDURE BOB

AS

PRINT 'BOB'

GO

Gets created as a system stored procedure.

Any ideas what would cause that and/or how to fix it?

Thanks,
Jason

View 16 Replies View Related

Stored Procedures Getting Created As System

Nov 8, 2007

I need to create a stored procedure in the master database (yes, I know it's not that good of an idea). I'm working with SQL 2K5, SP2 Whenever I create it, it is marked as a system stored procedure no matter what I name it, what schema I put it in, or what user I use to create it (sysadmin or minimal permissions).

As soon as I create it, if I do any of the following, I can see it to be a system stored procedure and not a regular user sp.

1) SELECT * FROM sys.objects where is_ms_shipped = 1
2) SELECT * FROM sys.procedures where is_ms_shipped = 1
3) Looking in SSMS... There is a special folder for system stored procedures in SSMS, and mine is in there.

At least in my case, the only thing it hurts is that you have to be a sysadmin to execute that stored procedure (and I need to have a non sysadmin be able to execute it). Other than that, it executes normally when run by a sysadmin.

Any suggestions on why this is happening? It's only happening on 1 out of about 80 SQL servers we have.

View 4 Replies View Related

Can't Find My Stored Procedures I've Created

Jun 24, 2007

Hi
im using sqlserver studio managment
i goto a specific database and goto stored procedures and add stored procedures from context menu and created successfully .

i try to find it between procedures but not found i try to create it again gives me error that already found so.
where i can get my created stored procedures from sqlserver studio managment?

thanks in advance.

View 1 Replies View Related

About Default Created System Stored Procedures

May 12, 2008

i found that some stored procedure get created automatically in sqlserver 2000 (system stored procedures) ,while doing my work i accidentally deleted those stored procedures can any body answer following questions1: why these stored procedures are there and automatically created2: what happen en if i deleted those stored procedures3: how to recreate those stored procedures with limited user permission thanks in advance 

View 2 Replies View Related

Temp Tables Created In Stored Procedures

Feb 12, 2008

Are they unique to a user/session? Like if 2 users simultaneously run the stored procedure?
TIA!

View 13 Replies View Related

Dynamically Named Stored Procedures

Oct 18, 2007

I have a set of stored procedures that return a fixed list of columns from a fixed list of parameters.

Is it possible to use 1 Report file to display these reports, and just switch the stored procedure name at runtime within .NET code, or will this reset the parameter list and/or column list?

View 4 Replies View Related

Reporting Services :: How To Find Out Last Created Stored Procedures

Jun 8, 2015

How to find out last created stored procedures in sql.

View 5 Replies View Related

SQL Server 2008 :: Execute Stored Procedures Dynamically

Mar 5, 2015

I have a column in a table, which have the stored procedure name stored in each row. Now, I need to execute each SP in the table dynamically. I'm trying to construct a SQL but not able to fire them!!

DECLARE @sql VARCHAR(MAX)
SELECT @sql = STUFF((SELECT '; GO EXEC ' + StoredProcedureName + '' FROM MyTable FOR XML PATH ('')),1,5,'')
print @sql
EXEC sp_executesql @sql

View 2 Replies View Related

Integration Services :: Execute Stored Procedures In Parallel And Dynamically?

Feb 19, 2015

I have a stored procedure. It can be executed like this

exec test @p = 1;
exec test @p = 2
exec test @p = n;
n can be hundred.

I want the sp being executed in parallel, not sequence. It means the 3 examples above can be run at the same time.
If I know the number in advance, say 3, I can create 3 different Execution SQL Tasks. They can be run in parallel.

However, the n is not static. It is coming from a table. 

How can I execute Stored Procedures in PARALLEL and DYNAMICALLY ?

I think about using script task. In the script, I get the value of n, and the list of p, from the table, then running a loop with. In the loop, I create a threat and in the threat, I execute the sp like : exec test @p = p. So the exec test may be run parallel. But I am not sure if it works.

View 4 Replies View Related

Using A Stored Procedure To Query Other Stored Procedures And Then Return The Results

Jun 13, 2007

Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB  multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived,     I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID,    S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName',    T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID    FROM [Item].ItemDetails I    INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID    INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived,     I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID    FROM [Item].ItemDetails IOr something like that... Any thoughts? 

View 3 Replies View Related

How To Return A Table With Stored Procedures?

Jun 20, 2007

i have a procedure like below but it dosen't return any tableSELECT Hardwares.HWID, Hardwares.Title
FROM Hardwares INNER JOIN
Category ON Hardwares.CategoryID = Category.CategoryID
WHERE (Category.Type LIKE '%Hardware%') AND (Category.Title = @Title)   

View 3 Replies View Related

Return Distinct Values From Stored Procedures

Aug 17, 2006

I need to somehow filter the results of my stored procedure to return the distinct "OrderNum" values. I'm using SQL database and I'm look for a way to alter the results from the stored procedure. My stored procedure rptOpenOrderLines currently returns all invoices (items under a OrderNum). I want to somehow filter those results to return one and only one of those "OrderNum" variables from the invoices. The tricky part is that I need to somehow find a way to do this without going into my database and directly altering the SQL stored procedure. I would be happy for any recommendations/ideas. Thanks!

View 3 Replies View Related

Stored Procedures - How To Return ERRORS To VB.NET Code

Jun 6, 2008

 Hi all, if have problem to display error message in vb.net that comes from a stored procedure. IF ...... then    msgbox "ERROR at Update"    returnvalue = SUCCES ELSE    Msgbox "Successfull Updated"    returnvalue = ERROREND IFHow can I return values from Stored Procedure to VB.NET and then give to User a Message that the Update was successful or not.Thanks to all   

View 2 Replies View Related

Stored Procedures Return Ing Mutiple Rows

Jul 29, 2006

hello. I am trying to speed up my asp.net caledar.

I need a Stored Procedure that takes a Parameter.
the Parameter will be a Date.

then I want sql server to return all the classes that will happen on that date in formated string so i can display in the Asp.net caledar.

the table looks like this
classid
classname
classdate
classtime
(there are more than one class happening on the same day)


I need the retuen value to look like this

"Yoga 9:00am" & "<p>" & "Jazz 11:00am" & <p>


can someone help me or point me to a sample. this is really hard. thank you

View 6 Replies View Related

How To Return Multiple Values From Stored Procedures

Jun 22, 2007

How to return multiple values from stored procedures to reports in sql server 2005

View 5 Replies View Related

SQL 2005 Stored Procedures Return No Rows

Sep 19, 2006

I am trying to execute a simple SELECT statement in a stored procedure from within Visual Studio 2005. The procedure executes but returns no rows. (There are rows in the table and a VIEW using the same SELECT statement does return the rows.)

I added a PRINT statement to the stored procedure to see if it executed and it did print my text in the output window as expected.

The SQL server is running on Windows Server 2003 with latest service packs and patches. I had detached the database while doing maintenance on our system and re-attached it later.

I can not find any reason why it refuses to return rows.

Larry

View 4 Replies View Related

Question On Return Codes And Rollback For Stored Procedures.

Oct 19, 2001

I need to execute 10 stored procedures one after the other in a sequence,but need to roll back if any one of them fails and discontinue further execution.Example: if sp#3 fails it should roll back alll the changes made by sp# 1 and sp# 2 and also should not continue executing the rest of them. Any ideas?
Thanks.

View 1 Replies View Related

How Stored Procedures Return Multiple Result Sets?

Jan 28, 2007

hi

i read that stored procedures can return multiple result sets?how is that?

thanks in advance.

View 4 Replies View Related

Code Inside! --&> How To Return The @@identity Parameter Without Using Stored Procedures

Oct 30, 2005

Hi.here is my code with my problem described in the syntax.I am using asp.net 1.1 and VB.NETThanks in advance for your help.I am still a beginner and I know that your time is precious. I would really appreciate it if you could "fill" my example function with the right code that returns the new ID of the newly inserted row. 
Public Function howToReturnID(ByVal aCompany As String, ByVal aName As String) As Integer
'that is the variable for the new id.Dim intNewID As Integer
Dim strSQL As String = "INSERT INTO tblAnfragen(aCompany, aName)" & _                                    "VALUES (@aCompany, @aName); SELECT @NewID = @@identity"
Dim dbConnection As SqlConnection = New SqlConnection(connectionString)Dim dbCommand As SqlCommand = New SqlCommand()dbCommand.CommandText = strSQL
'Here is my problem.'What do I have to do in order to add the parameter @NewID and'how do I read and return the value of @NewID within that function howToReturnID'any help is greatly appreciated!'I cannot use SPs in this application - have to do it this way! :-(
dbCommand.Parameters.Add("@aFirma", aCompany.Trim)dbCommand.Parameters.Add("@aAnsprAnrede", aName.Trim)
dbCommand.Connection = dbConnection
TrydbConnection.Open()dbCommand.ExecuteNonQuery()
'here i want to return the new ID!Return intNewID
Catch ex As Exception
Throw New System.Exception("Error: " & ex.Message.ToString())
Finally
dbCommand.Dispose()dbConnection.Close()dbConnection.Dispose()
End Try
End Function

View 7 Replies View Related

Stored Procedures IF Statement

Sep 12, 2007

I have a webpage with a grid view in it that has sorting and filtering options.  I am trying to take the sql statement and turn them into stored procedures.  The problem I am having is converting this into a stored procedure.
SQL = "Select CustSite.CustSite, FileInfo.FileID, FileInfo.Date, FileInfo.OriginalFileName, ReportType.ReportType, Equip.Equip, Tech.Name From [CustSite], [FileInfo], [ReportType], [Equip], [Tech] Where(FileInfo.CustomerID = '" & Session("CustomerID") & "') AND (CustSite.CustSiteID IN (Select Customer_CustSite.CustSiteID From [Customer_CustSite] Where(CustomerID = '" & Session("CustomerID") & "'))) AND (ReportType.ReportTypeID = FileInfo.ReportTypeID) AND (Tech.TechID = FileInfo.TechID) AND (Equip.EquipID = FileInfo.EquipID) AND (Equip.CustSiteID = CustSite.CustSiteID)"
If Site.SelectedItem.Value <> "" Then
SQL += " AND (CustSite.CustSiteID = '" & Site.SelectedItem.Value & "')"
End If
If Date_Set.SelectedItem.Value = "Single" Then
SQL += " AND (FileInfo.Date = '" & Start_Date.SelectedItem.Value & "')"
ElseIf Date_Set.SelectedItem.Value = "Between" Then
SQL += " AND (FileInfo.Date Between '" & Start_Date.SelectedItem.Value & "' AND '" & End_Date.SelectedItem.Value & "')"
End If
If Report_Name.SelectedItem.Value <> "" Then
SQL += " AND (FileInfo.FileID = '" & Report_Name.SelectedItem.Value & "')"
End If
If Report_Type.SelectedItem.Value <> "" Then
SQL += " AND (FileInfo.ReportTypeID = '" & Report_Type.SelectedItem.Value & "')"
End If
If Equipment.SelectedItem.Value <> "" Then
SQL += " AND (FileInfo.EquipID = '" & Equipment.SelectedItem.Value & "')"
End If
If Tech.SelectedItem.Value <> "" Then
SQL += " AND (FileInfo.TechID = '" & Tech.SelectedItem.Value & "')"
End If
 
 Can someone point me in the right direction?  Thanks in advance for the help.

View 1 Replies View Related

If Statement In Stored Procedures Help

Jun 23, 2008

Hi all. This has kept me up all night! I'm trying to populate a datgrid with multiple tables with relating keys in each table.

For ease, I've made the databases hopefully understandable.

DoctorsDB - (ID, Name, Phone)
FriendsDB - (ID, Name, Phone)
PetsDB - (ID, Name)
PeopleISawTodayDB - (MyID, MyName, DocID, FriendID, PetsID)

Now I need to generate a datagrid with the following details:
MyID, MyName, Doc's Name, Friend's Name, Pet's Name.

I have some success with the following code:

PROCEDURE dbo.PeopleISawTodayDB
@ID as Int
AS
BEGIN
SELECT
PeopleISawTodayDB.MyID, PeopleISawTodayDB.MyName, PeopleISawTodayDB.DocID, PeopleISawTodayDB.FriendID, PeopleISawTodayDB.PetID, DoctorsDB.ID, DoctorsDB.Name, FriendDB.ID, FriendDB.Name, PetDB.ID, PetDB.Name
FROM
PeopleISawTodayDB INNER JOIN
DoctorDB ON PeopleISawTodayDB.DocID = DoctorDB.ID INNER JOIN
FriendDB ON PeopleISawTodayDB.FriendID = FriendDB.ID INNER JOIN
PetDB ON PeopleISawTodayDB.PetID = Pet.ID
WHERE PeopleISawTodayDB.JobID=@ID
END



BUT it will only make a row appear if there are ID's in each of the PeopleISawTodayDB respective ID fields.

If I want to leave one blank (as in I didn't see that person that day), I would like it to still find the other details and populate the datagrid.

Can anyone lend a coding hand???? Cheers

View 7 Replies View Related

Dynamically Created Dataset

Jan 26, 2007

I am building a mailing list report.

I have the report all built to display name, address, etc and this works well if i build the dataset in RS.

Problem:
I want to use this same report to build mailing list for any group of people the user selects while using a c# application.

Question:
Is there anyway to build a dataset in an application then send it to RS?

thanks
lucas

View 5 Replies View Related

Calling Stored Procedures In SQL Statement

Aug 25, 2007

How can we call Stored Procedure inside any SQL Statement
For Example.
If we have procedure name sprocCurrentPriority
select * from tablename where colmunname = exec sprocCurrentPriority

View 7 Replies View Related

One Stored Procedures (SQL Statement) Question

May 9, 2005

Hi Everyone,
I have a Auto database table which store company auto business records.
If I want to know how many BMW still in store, I use Stored Procedure as
<code>Select Count(*) From AutoAd Where Make = 'BMW' and InStore = 1.
</code>
Now I want to know how many autos in store for all make (BMW, AUDI, HONDA, FORD...).  Does single stored procedures can instead twenty stored procedures to solve this question?  If can, please help me to show your stored procedures and how to reture the result to .NET program.
Thanks,
Lin

View 1 Replies View Related

Stored Procedures IF Statement (SQL2005)

Jan 30, 2006

Hello, I need to use a if statement in stored procedures.

What I need to know is the proper syntax because anything I try does not work.

I need to check if 24 hours passed:
datediff(hh, Users.LastLogin, GetDate())

Should return the number of hours that has passed. I need to use this in the IF statement, and I need to check if it's > 24.

Thanks =)!

View 2 Replies View Related

USE Statement In Stored Procedures Two Databases, How Please?

May 23, 2008

This works great in a query:


USE KCADOWNLOAD

USE OUTHOUSE

GO

DELETE FROM OUTHOUSE.KCA_BLDGS_ACCES

INSERT INTO OUTHOUSE.OUTHOUSE.KCA_BLDGS_ACCES

(MAJOR, MINOR, ACCYTYPE, ACCYDESCR,

QUANTITY, SIZE, UNIT, GRADE, EFFYR, PCNTNETCONDITION, ACCYVALUE, DATEVALUED, UPDATEDBY,

UPDATEDATE,PID)

SELECT MAJOR, MINOR, ACCYTYPE, ACCYDESCR,

QUANTITY, SIZE, UNIT, GRADE, EFFYR, PCNTNETCONDITION, ACCYVALUE, DATEVALUED, UPDATEDBY,

UPDATEDATE,PID

FROM KCADOWNLOAD.dbo.KCA_BLDGS_ACCES AS KCA_BLDGS_ACCES_1



But the stored procedure only creates ONE Use statement:


USE [KCADOWNLOAD]

...etc.


I assume I can run an SP utilizing two databases...


The SP fails because it hasn't got a clue what OUTHOUSE is...


Thank you!!!

View 3 Replies View Related

Using DB Cursor In Conjunction With Dynamically Created Sql

Oct 29, 2004

OK...

I have an sql statement that i have manually built as a string. normally i would just return a recordset with it using



Set @sqlBuild = 'SELECT * FROM ' + @Table + ' WHERE value = ' + @value

EXEC (sqlBuild)



something like that works fine...

however, i would like to know if i can instead of just executing the SQL statement use that dynamically built statement with a cursor somehow.

basically what i'm trying to do is loop through that result set with a cursor instead of just simply executing it.

Any suggestions?

View 1 Replies View Related

... Executing Stored Procedures In An INSERT Statement ...

Aug 28, 2002

I am trying to simulate the <sequence name>.nextval of oracle in SQL Server 2000.

The situation is that i need to be able to run programmatically INSERT statements. In Oracle I am able to do INSERT INTO TABLE_A (ID, NAME) VALUES (TABLE_A_SEQUENCE.NEXTVAL, 'MIKKO') in a single prepared statement in my code.

I know that to recreate this in SQL Server 2000 I need to create a stored procedure and table to set up a way to generate "the next value" to use in my INSERT. but the schema below forces me to do my insert in 2 steps (first, to get the next value. second, to use that value in the INSERT statement), since I cannot execute the stored procedure inside my INSERT statement.

Is there any way for me to generate values within my INSERT statement that would simulate Oracle's <sequence name>.nextval and allow me to execute my INSERT in 1 line of code?

TABLE
-----
CREATE TABLE sequences (
-- sequence is a reserved word
seq varchar(100) primary key,
sequence_id int
);

MS SQL SERVER STORED PROCEDURE:
-------------------------------
CREATE PROCEDURE nextval
@sequence varchar(100)AS
BEGIN
-- return an error if sequence does not exist
-- so we will know if someone truncates the table
DECLARE @sequence_id int
set @sequence_id = -1

UPDATE sequences
SET @sequence_id = sequence_id = sequence_id + 1
WHERE seq = @sequence

RETURN @sequence_id
END

View 1 Replies View Related

Calling Stored Procedures In A Select Statement

Feb 26, 2004

I am trying to call a stored procedure inside a SQL SELECT statement. Has anybody had to do this in the past? I have a SELECT statement in a Microsoft Access database and I need that SELECT statement to call the stored procedure in the SQL server. Any help would be appreciated

View 4 Replies View Related

Dynamically Listing Created Temp Tables

Jan 21, 2002

Hi,

I want to create a nested SP which will dump out the results of All Temp Tables that are currently created in the session. The purpose of this is for createing a generic debugging SP which will write the contents of all temp tables to a debug table (when a certain flag is set).

I need to know how to:

- Get a list of all temp tables created
- Get a list from each temp table of the columns created.

Hope someone can help!

Cheers,

Andrew

View 1 Replies View Related

Using A Dynamically Created Parameter In Another Dataset Query

Jul 27, 2007



It's been a while since I used Reporting Services so I'm sure this is really straight forward. Basically I have the following report parameters:

reportMonth - Just a non-queried list of months for the user to select
reportYear - Generated using the following query from dataset "Years":

declare @curYear int
set @curYear = 2000
declare @yearTable table (repYear int not null)
while @curYear <= year(getdate())
begin
insert into @yearTable(repYear) values (@curYear)
set @curYear = @curYear + 1
end
select * from @yearTable

This generated a list like:

2000
2001
2002
etc.

I then have a 2nd dataset "Main" which I'd like to use both the reportMonth and reportYear parameters in once they've been generated. How do I go about setting this up and referencing the parameters? I've tried a few things but nothing seems to be working.

Thanks

View 10 Replies View Related







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