I have created multiple stored procedures that search different tables for similiar information.
Is it possible to have one main stored procedure that calls and executes each of these individual stored procedures and then use the UNION keyword to combine the results?
For example
Code:
CREATE PROCEDURE [dbo].[Return_Detail]
@IDVarChar(200)
AS
--Get the 1st Detail
EXECReturn_1st_Detail @ID = @ID
UNION
--Get the 2nd Detail
EXECReturn_2nd_Detail @ID = @ID
GO
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?
I need to keep track of the number of hits on a particular page. Im using a stored Procedure
What I want to do is get the number of hits and increment it by one :)
ie: Sub Procedure should be like below
SELECT noOfHits WHERE pageName = 'bla bla'
noOfHits = noOfHits + 1 etc.
Also, some of the pages will be added and deleted all the time, so before I increment the noOfHits variable I need to check that the pageName 'bla bla' exists. AND if it doesnt I need to create a pageName called 'bla bla'
What I need to do in essence is:
1. Check that a particular row exists. if it doesnt create it. 2. Increment a value (by one) to a column in this particular row.
Phew. Hope you got that. Any ideas much appreciated,
Has anyone had a problem where the stored procedure does not return the same results as compared to running the same SQL in ISQL? This happens when rows are added to a table that's in a join and the SP doesn't return those rows. Once it starts happening, it will not work consistently, but it doesn't always happen when new rows are added to a table. It has happened to many stored procedures and with many different tables. sp_recompile won't fix the problem. Only dropping and re-adding the SP will fix the problem with that stored procedure. We are using SQL 6.5.
we've got a Windows Server 2003 environment with SQL Server 2000 Sp 3.
A stored procedure selects specific data from a user-table which depend on the user executing it. The users are granted execute permission on the stored procedure. But execution fails, if the user is not granted select permission on the user-table, too.
The problem is, that the user must not have the permission on all data in the user-table but on the data concerning him.
In earlier versions of SQL Server and Windows the execute permission has granted sufficient rights to select from the underlying tables. How can this be re-established?
I tried this morning to check some of the system stored procedures and ran into trouble. Only four of them executed: sp_alterdiagram, sp_creatediagram, sp_dropdiagram and sp_helpdiagramdefinition. I could not check all the rest, I did it selectively. The typical error message was: Invalid object on RETURN statement. Some had syntactical errors. sp_ActiveDirectory_Obj Invalid object name 'sys.sp_ActiveDirectory_SCP' Line 171 sp_ActiveDirectory_SCP Invalid object name 'sys.sp_ActiveDirectory_SCP' Line 171 sp_ActiveDirectory_Start Invalid object name 'sys.sp_ActiveDirectory_Start' Line 19 Invalid object name 'sys.sp_add_agent_parameter' Line 60 Invalid object name 'sys.sp_add_agent_profile' Line 123 Invalid object name 'sys.sp_add_data_file_recover_suspect_db' Line 17 sp_addalias Msg 102, Level 15, State 1, Procedure sp_addalias, Line 44 Incorrect syntax near '%'. Msg 195, Level 15, State 10, Procedure sp_addalias, Line 64 'get_sid' is not a recognized built-in function name. Msg 102, Level 15, State 1, Procedure sp_addalias, Line 78 Incorrect syntax near '%'. Msg 102, Level 15, State 1, Procedure sp_addalias, Line 119 Incorrect syntax near '%'. sp_bindefault Msg 102, Level 15, State 1, Procedure sp_bindefault, Line 95 Incorrect syntax near '%'. Msg 102, Level 15, State 1, Procedure sp_bindefault, Line 134 Incorrect syntax near '%'. Msg 102, Level 15, State 1, Procedure sp_bindefault, Line 182 Incorrect syntax near '%'. Msg 102, Level 15, State 1, Procedure sp_bindefault, Line 208 Incorrect syntax near '%'. Msg 102, Level 15, State 1, Procedure sp_bindefault, Line 228 Incorrect syntax near '%'. Msg 102, Level 15, State 1, Procedure sp_bindefault, Line 264 Incorrect syntax near '%'. Msg 102, Level 15, State 1, Procedure sp_bindefault, Line 273 Incorrect syntax near '%'. sp_databases Invalid object name 'sys.sp_databases'. Line 6 sp_tables Invalid object name 'sys.sp_tables'
Is there a chance that these errors are due to the fact that I executed them without parameters?
Hi. Does anyone know how to display the results if i execute "xp_fixeddrives, xp_availablemedia and xp_subdirs" commands with VC++ 6.0? I can't obtained the results using Recordset class. Can someone help me? Thank you.
Hi I am currently using SQL server 2005 express edition for a website I have created using Asp.Net 2. For this website I call stored procedures that I have created in the databse to return any page data. However, I keep getting error messages say that the login does not have execute permission for the stored procedure. In Sql Server 2005 there does not seem to be an easy way to grant permissions to a stored procedure as you add them. I say this because when I used Sql Server 2000 I would just add the stored procedure, rigth click on it and grant permission to the user. Now this does not seem to be the case with the new version of sql server and I was just wondering whether there is now a new, easy way of doing this. If anyone can point me in the right direction on this... I have managed to get this working by going into the properties of the users atached to the database, adding a list of stored procedures to the "scalables" area and individually ticking the execute checkboxs. However, when I return to add a new stored procedure, the list has disapeared. Is this a bug with Sql server 2005? Thanking you in advance
Hey guys,I'm pretty new to SQL configuration, and I need to give EXECUTEpersmissions for one of the SQL user roles. I am running SQL 2005Management Studio Express - free version. I found the list of mystored procedures, but I can not locate any permissions screen. Cansomeone help point me in the right direction? Thanks!
I'm running this legacy DTS package under SQL Server 2005, and need to make some changes.
I have some (say, 3) stored procedures, and they all have one same parameter. Each SP returns a dataset. I need to import each dataset to a table using DTS. Both the SPs and the destination tables are in the same database.
So, I created an "Execute SQL Task" and typed in "SQL Statement" something like this:
INSERT INTO TABLE TABLE1 EXEC SP1 ? GO INSERT INTO TABLE TABLE2 EXEC SP2 ? GO INSERT INTO TABLE TABLE3 EXEC SP3 ? GO
And the execution returns an error: No value given for one or more required parameters.
Is it possible to assign a parameter to all three SPs? What is the work out if I don't want to change the SPs, and I don't want to create 3 Execute SQL Tasks?
In my SPs, I commonly have a situation, where a SELECT statement gets a single scalar value (e.g. SELECT Name FROM Employee WHERE id=@id) from a table.
Because the result is still a relation, I cannot process it directly or assign the result to a variable (like set @name = SELECT Name FROM Employee WHERE id=@id)
So, how can I process the results of the statement in this case.
In some other cases, the result is actually a relation. And I want to iterate over all rows, processing each row's columns. (I know this smells of ADO.NET, but how can I help it if I am coming from that background)...
The point is I want to do all this in T-Sql on server side!!!
I surfed a lot of the internet piecing this together. I have a database that gets copied every night from a live database so users can run queries against it, so I needed to be able to recreate a stored procedure from within ASP.NET. Also, I needed to take information I was importing from a .CSV file and compare it using the stored procedure to find specific information and import it into another database. I was able to do this using the SQLDMO reference object. Here are some clipits of what you need. Imports SQLDMOPrivate myServer As New SQLServer Dim serverName As String = System.Configuration.ConfigurationManager.AppSettings("Server").ToString()Dim userName As String = System.Configuration.ConfigurationManager.AppSettings("UserName").ToString() Dim password As String = System.Configuration.ConfigurationManager.AppSettings("Password").ToString() Dim storedProcedure As New StoredProcedure Dim qresults As SQLDMO.QueryResults Example Stored Procedure Try myServer.Connect(serverName, userName, password) storedProcedure.Text = "IF EXISTS (SELECT * FROM sysobjects WHERE name='memb_proc' AND user_name(uid)='dbo') DROP PROCEDURE dbo.memb_proc"myServer.Databases.Item("<databasename>", "dbo").StoredProcedures.Add(storedProcedure) storedProcedure.Text = "CREATE PROCEDURE dbo.memb_proc @lastname varchar(50) = '', @firstname varchar(50) = '', @zip varchar(5) = '', @membrowno varchar(50) OUTPUT AS Select @membrowno = rowno from memb where lastname LIKE @lastname and name LIKE @firstname and zip1 LIKE @zip "myServer.Databases.Item("<databasename>", "dbo").StoredProcedures.Add(storedProcedure) Label1.Text = "Creation of stored procedure successful"Catch ex As Exception Label1.Text = "Creation of stored procedure failed" Finally myServer.DisConnect()End Try Run Stored Procedure and Obtain Results myServer.Connect(serverName, userName, password) qresults = myServer.ExecuteWithResults("USE [databasename] DECLARE @return_value int,@membrowno varchar(50) EXEC @return_value = [dbo].[memb_proc] @lastname = N'" & Last & "', @firstname = N'" & First & "',@zip = N'" & ZIP & "', @membrowno = @membrowno OUTPUT SELECT @membrowno as N'@membrowno'")For num = 1 To qresults.Rows MemberRowNo = qresults.GetColumnString(num, 1) Next myServer.DisConnect()
We are using a third party tool that does not store passwords in an encrypted format therefore we created a user with minimal rights. Isn't there a way to grant "execute any stored procedure" to a user/Login? Do we really have to grant execute on each procedure to the user? And then do the same for each new store procedure? The only other option we have found to be able to "see" and execute the stored procedures is by granting "db_owner". I would think that would negate the user being minimal rights.
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
BEGIN TRANSACTION @Tran1 ¦¦¦¦¦. ¦¦¦¦¦¦ ¦¦¦¦.
INSERT INTO [tabloA] (, ,) SELECT ,, FROM [tmptabloA] WHERE ......
¦¦¦ ¦¦¦ ¦¦¦.
DELETE FROM [tmptabloA]
COMMIT TRANSACTION @Tran1
When user [nuran] execute the procedure sp_yordam by a VB program, the procedure use [dbo].tmptabloA not [nuran].[tmptaboA]. If there are data in the [dbo].tmptabloA, the procedure insert data to [dbo].tabloA from [dbo].tmptabloA. But when I checked user name in the procedure during execution, the user was [nuran].
If I write the procedure like that:
(2) create PROCEDURE [dbo].[SP_tmpSil] AS
declare @tablo1 as varchar(50), DECLARE @sil as nvarchar(max) select @tablo1='[tmptabloA]'
And it executed by user [nuran],then it used the correct table [nuran].tmptabloA
Is there any way to use users table in an stored procedure without using the user name : (3) create PROCEDURE [dbo].[SP_yordam] AS BEGIN
BEGIN TRANSACTION @Tran1 ¦¦¦¦¦. ¦¦¦¦¦¦ ¦¦¦¦.
INSERT INTO [tabloA] (, ,) SELECT ,, FROM [nuran].[tmptabloA] WHERE ......
¦¦¦ ¦¦¦ ¦¦¦.
DELETE FROM [nuran].[tmptabloA]
COMMIT TRANSACTION @Tran1
I don't want to use (2) and (3) code methods, I prefer to use (1) script. Is there any compilation method, or any aditional way for using script (1) with correct user rights?
While doing maintenance on a development server we detached a database and later reattached it to the same server.
For some unknown reason web services execute but return no results. There are no errors reported in event viewer. I connected to the database in Visual Studio 2005 and attempted to execute a simple select statement but it returns no row.
It appears to execute the stored procedure but does not return any data. I have cut and pasted the select statement into a View which does execute.
Lastly I just created a new store procedure to test the database and it also does not return any rows. This is all very strange.
awhile back someone (i think it was darren) submitted a post which had a code example of using sql server agent stored procedures to execute a package. i searched for the post, but i didn't find it.
anyway, can someone please re-post that example or provide a similar one?
Variable Name : User::IntValue Direction: ReturnValue Data Type: Long ParameterName: 0
I am getting the following error, when I run this package.
[Execute SQL Task] Error: Executing the query "EXEC ? = [dbo].[SetSLATimePriority]" failed with the following error: "Invalid parameter number". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I am not able to figure out, where exactly the problem is.. Can some one please help me out?
I have a stored procedure which simply does a SELECT on a table. This table has as a column a uniqueidentifier, which is not part of the PK. If I execute this procedure with the SQL Server tools, it works fine and returns the expected results. If I execute this SP with Visual Studio, or ASP.NET, no results are returned and the following comes back:Running [dbo].[spServiceDetail_Get] ( @ServiceDetailID = <DEFAULT>, @VictimWitnessID = <DEFAULT>).ServiceDetailID VictimWitnessID --------------- -------------------------------------- No rows affected.(1 row(s) returned)@RETURN_VALUE = 0Finished running [dbo].[spServiceDetail_Get]. Any ideas what is going wrong? This seems to be a common problem for many of our tables with a uniqueidentifier in them, and is specifically with VS 2005 and ASP.NET ObjectDataSources.
Hi, all I'm using Sql server 2000 I want to make select statement dynamically and return table using function. in sp, I've done this but, in function I don't know how to do so. (I have to create as function since our existing API..)
Following is my tials... 1. alter Function fnTest ( @fromTime datetime, @toTime datetime) RETURNS Table AS
RETURN Exec spTest @from, @to GO
Yes, it give syntax error..
2. So, I found the following
From Sql Server Books Online, Remark section of CREATE FUNCTION page of Transact-SQL Reference , it says following..
"The following statements are allowed in the body of a multi-statement function. Statements not in this list are not allowed in the body of a function: " ..... * EXECUTE statements calling an extended stored procedures.
So, I tried.
alter Function fnTest ( @fromTime datetime, @toTime datetime) RETURNS Table AS
RETURN Exec master..xp_msver GO
It doesn't work... syntax err...
Here I have quick question.. How to execute statements calling an extended stored procedures. any examples?
Now, I'm stuck.. how can I create dynamic select statement using function?
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.
Hello All,I tried to set the access permissions for debugging stored procedure by reading the articlehttp://msdn2.microsoft.com/en-us/library/w1bhybwz(VS.80).aspxandhttp://technet.microsoft.com/en-us/library/ms164014.aspxI have tried to add the role to sysaminas follows1)SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'sp_sdidebug'(to find the sp)Error:--The stored procedure not found2)sp_addsrvrolemember 'Developmentswati.jain', 'sysadmin' though this is executed successfuly . Error is still persisting Cannot debug stored procedures because the SQL Server database is not setup correctly or user does not have permission to execute master.sp_sdidebug.
I'm trying to create a list of orders in my db that has been created correctly (some orders are not dealt with correctly...) An order should go from "open -> assigned" to "assigned -> responded" status.
I got the following query:
select org.name, count(order) AS correct, NULL AS Total from order left join orderstatus o1 on order.id = o1.order_id left join orderstatus o2 on order.id = o2.order_id left join org on order.orgid on user.id where o1.status = 'Open -> Assigned' and o2.status = 'Assigned -> Responded' and o1.time_stamp < o2.time_stamp
This gives me a list of all organisations with the correct number of orders in the system...
But now I need to add the total number of tickets they got in the system. So I was thinking about a union with a query without the were constraints
UNION 'with the above query select org.name, NULL AS correct, count(order) AS Total from order left join orderstatus o1 on order.id = o1.order_id left join orderstatus o2 on order.id = o2.order_id left join org on order.orgid on user.id
..but that gives me a list like this:
name correct total org1 324 NULL org1 NULL 423
How can I combine them, or maybe doing it a better way?
I have a simple sql select statement that looks like this. Select Column1+ ' ' + Column2 As SpecFROM Table both columns are varchar's and the output i get is something like this.Spec-----------------------AaBbCc What I would like to return as my results is this: Spec--------------------AaBbCc I hope this makes sense. I can I accomplish that?Thanks!
The users to groups relationship is many-to-many, which is why I created the intermediate table. I would like to return a recordset like: userid, name, groupids 12344, 'Bob', '123,234,345'
If I try to just select groupid from groupadmins: select userid, name, (select groupid from groupadmins where groupadmins.userid = users.userid) as groupids from users
then I'll get an error that a subquery is returning multiple results. Some users are not group admins and those that are may have a single or multiple groups. The only thing I can think of is to select the groupids seperately and use a cursor to loop through the results and build a string with the groupids. Then I would select the string with the rest of the fields that I want for return. Is there a better way to do this?
