I'm fairly new to MSSQL 7.0, and am having a problem. I would like to create a Stored Procedure that builds a Union of the result sets from two Stored Procedures. In reality, it is two calls to a single stored procedure, with different parameters.
I can't find any documented method to actually perform any operations on the result set of a stored procedure. Is there any way to get a SELECT statement to return the results of a Stored Procedure call?
I have a query which does 3 selects and Union ALLs each to get a final result set. The performance is unacceptable - takes around a minute to run. If I remove the Union All so that the result sets are returned individually it returns all 3 from the query in around 6 seconds (acceptable performance).
Any way to join the result sets together without using Union All.
Each result set has exactly the same structure returned...
Query below [for reference]...
WITH cte AS ( SELECT A.[PoleID], ISNULL(B.[IsSpanClear], 0) AS [IsSpanClear], B.[SurveyDate], ROW_NUMBER() OVER (PARTITION BY A.[PoleID] ORDER BY B.[SurveyDate] DESC) rownum FROM[UT_Pole] A LEFT OUTER JOIN [UT_Surveyed_Pole] B ON A.[PoleID] = B.[PoleID]
Hi, i have encountered a problem in which i am sure has a simple answer. I am used to MySQL and am new to SQL Server and would appreciate any expert advice. Thanks
I have 4 stored procedures and need to get the data that they generate out of all of them and place them into one resulting table.
here is the code i had for it:
USE [SQLexport] GO /****** Object: StoredProcedure [dbo].[AllSpirometry] Script Date: 05/31/2007 17:05:03 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO ALTER PROCEDURE [dbo].[AllSpirometry] AS BEGIN select * from dbo.All05 UNION select * from dbo.All075 UNION select * from dbo.All01over6 UNION select * from dbo.All075under6 END
and here are the errors that occurred:
Msg 208, Level 16, State 3, Procedure AllSpirometry, Line 3 Invalid object name 'dbo.All05'. Msg 208, Level 16, State 3, Procedure AllSpirometry, Line 3 Invalid object name 'dbo.All075'. Msg 208, Level 16, State 3, Procedure AllSpirometry, Line 3 Invalid object name 'dbo.All01over6'.
I have been told that this code will not work in SQL Server and been given a longggggg piece of code that will make it work by placing all data into a temporary table. I am adamant that such a long piece of code is not needed, surely there must be a way to get this to work very simply in SQL Server.
I would appreciate any help you can provide me, thanks
I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!
Hi All, What i'm trying to do is build a dynamic query where the like clause is the variable bit of the query. What I've done is to create 4 varchar variables of length1000, and a variable to hold the result of the concatenated variables, which is defined as length of 4000. I've checked the length of the resultant query and its comes in at arount the 450 charcter lenght, but when I run the stored proc it truncates the @varfull around the point of the first % sign. I've tried various ways to create this query and it always truncated around the point of the % sign. Am I doing this right? can anyone point me in the right direction on how to do this with a sql statement, it works fine for text strings! Here's part of the code, I create the content of @var3 in a loop based on a string of words passed into the stored proc. Thanks for any help on this! regrads davej @var1 = 'SELECT COUNT(d.item_id) AS Expr1, d.item_id FROM tp_index_details AS d INNER JOIN tp_index ON d.idx_id = tp_index.idx_id '@var2 = 'WHERE (d.idx_id IN (SELECT idx_id FROM tp_index AS i WHERE (item_Text LIKE' @var3 = ''%london%' OR item_Text LIKE '%solicitor%'' @var4 = ' ) AND (subscription_id = 1000))) GROUP BY d.item_id ORDER BY d.item_id DESC' @varfull = @var1+@var2+@var3+@var4
I am in the very final stages of building a dating app for a client, I am totally stuck with the advanced search page. been googling for days with limited success
For the most basic of purposes I have added a few form fields to my search.aspx page; county (Dropdown list) min age (Dropdown list) max age (dropdown list) Smoker (check box) keyword (textbox)
My codebehind passes the vars to a stored procedure @county = me.county.selectedvalue etc My problem is the stored procedure I sort of have the following but I can't get it to run <code> ALTER PROCEDURE dbo.TEST_ADVANCED_SEARCH (@countyID int , @MaxAge varchar(100), @MinAge varchar(100),
We are migrating from a file-server Access Database to a SQL server backend and Access front end system. I'm using ADO to access the data off the server but am implementing most of the business logic in stored procedures. All logic was coded in VBA earlier but i'm having to move that to T-SQL for performance issues. In many cases I have dynamically constructed SQL statements in code but I'm having some trouble in T-SQL. How can I do this in T-SQL?
' This is some VB code that shows how the query differs based on a parameter. If Me![Sorting] = 1 Then Me![ControlNumber].RowSource = "SELECT [DVD_Projects_Table].[DVD_Number], [Title] & "" : "" & [ID_Number] AS Display,__ [DVD_Projects_Table].Date FROM [DVD_Projects_Table] __ WHERE ((([DVD_Projects_Table].System) = IIf([Forms]![DVD_Projects_Form]![SystemFilter] = 1, ""525"", ""625""))) And __ (([DVD_Projects_Table].Active) = IIf([Forms]![DVD_Projects_Form].[ActiveOnly] = True, Yes, __ [DVD_Projects_Table].[Active]))) ORDER BY [DVD_Projects_Table].Title;" Else Me![ControlNumber].RowSource = "SELECT [DVD_Projects_Table].[DVD_Number], [ID_Number] & "" : "" & [Title] AS Display,__ [DVD_Projects_Table].Date FROM [DVD_Projects_Table]__ WHERE ((([DVD_Projects_Table].System) = IIf([Forms]![DVD_Projects_Form]![SystemFilter] = 1, ""525"", ""625""))) And __ (([DVD_Projects_Table].Active) = IIf([Forms]![DVD_Projects_Form].[ActiveOnly] = True, Yes, __ [DVD_Projects_Table].[Active]))) ORDER BY Int(Right([ID_Number],Len([ID_Number])-4));" End If
This is the ideal sp that would do what I want but it is obviously incorrect. How can I get this logic implemented. I need to construct an SQL query based on the input parameters in a stored procedure.
CREATE PROCEDURE [procDVDProjectsList] @SortBy as bit, @ActiveOnly as bit, @SysFilter as integer AS SELECT CASE @SortBy /* Display Title first */ WHEN 0 THEN [DVD_Number], [Title] + " : " + [ID_Number] AS Display, [DVD_Projects_Table].[Date] /* Display Number first */ WHEN 1 THEN [DVD_Number], [ID_Number] + " : " + [Title] AS Display, [DVD_Projects_Table].[Date] END FROM [DVD_Projects_Table] WHERE CASE @SysFilter /* List all */ WHEN 0 THEN /* List only NTSC */ WHEN 1 THEN [DVD_Projects_Table].[System] = "525" /* List only PAL */ WHEN 2 THEN [DVD_Projects_Table].[System] = "625" END AND CASE @ActiveOnly /* List All */ WHEN 0 THEN /* List only active */ WHEN 1 THEN [DVD_Projects_Table].Active = True END ORDER BY CASE @Sortby /* Sort Alpha */ WHEN 0 THEN [DVD_Projects_Table].Title /* Sort Numeric */ WHEN 1 THEN Right([ID_Number],Len([ID_Number])-4) END
Hi i have a page whereby the user can make a search based on three things, they are a textbox(userName), dropdownlist(subcategoryID), and region (regionID). The user does not have to select all three, he or she can enter a name into the textbox alone and make the search or enter a name into the textbox and select a dropdownlist value, my question is how can i build this procedure, this is what another user suggested but i am having trouble; ALTER PROCEDURE [dbo].[stream_UserFind] ( @userName varchar(100), @subCategoryID INT, @regionID INT )AS declare @StaticStr nvarchar(5000)set @StaticStr = 'SELECT DISTINCT SubCategories.subCategoryID, SubCategories.subCategoryName,Users.userName ,UserSubCategories.userIDFROM Users INNER JOIN UserSubCategories ON Users.userID= UserSubCategories.userIDINNER JOINSubCategories ON UserSubCategories.subCategoryID = SubCategories.subCategoryID WHERE UserName like @UserName' if(@subCategoryID <> 0) set @StaticStr = @StaticStr + ' and SubCategories.subCategoryID = @subCategoryID 'if(@regionID <> 0) set @StaticStr = @StaticStr + ' and SubCategories.RegionId = @regionID ' exec sp_executesql @StaticStr )
Hi i have a page whereby the user can make a search based on three things, they are a textbox(userName), dropdownlist(subcategoryID), and region (regionID). The user does not have to select all three, he or she can enter a name into the textbox alone and make the search or enter a name into the textbox and select a dropdownlist value, my question is how can i build this procedure, I tried this but it didnt work;
Code:
ALTER PROCEDURE [dbo].[stream_UserFind]
(
@userName varchar(100),
@subCategoryID INT,
@regionID INT
) AS
declare @StaticStr nvarchar(5000) set @StaticStr = 'SELECT DISTINCT SubCategories.subCategoryID, SubCategories.subCategoryName, Users.userName ,UserSubCategories.userID FROM Users INNER JOIN UserSubCategories ON Users.userID= UserSubCategories.userIDINNER JOIN SubCategories ON UserSubCategories.subCategoryID = SubCategories.subCategoryID WHERE UserName like @UserName'
if(@subCategoryID <> 0) set @StaticStr = @StaticStr + ' and SubCategories.subCategoryID = @subCategoryID ' if(@regionID <> 0) set @StaticStr = @StaticStr + ' and SubCategories.RegionId = @regionID '
I have this stored procedure written up that basically builds a dataset by querying a bunch of tables using outer joins. Our problem now is that it seems it takes a while for the dataset to pull back across the network. We would hence like to filter that dataset by adding on to the query in the procedure dynamically. Heres the query from the proc below:
SELECTN_Client.Prefix, IsNull(dbo.N_CLIENT.SURNAME, '') + ', ' + IsNull(dbo.N_CLIENT.FIRST_NAME, '') AS Client_FullName, dbo.N_CLIENT.TITLE, dbo.N_COMPANY.COMPANY_NAME, dbo.N_BUSINESS_UNIT.BUSINESS_UNIT_NAME, dbo.N_DIVISION.DIVISION_NAME, dbo.N_REF_INDUSTRY.INDUSTRY_NAME, dbo.N_CLIENT.DIRECT_PHONE, dbo.N_CLIENT.EMAIL, dbo.N_CLIENT.TIER_ID, (SELECT COUNT(Client_ID) FROM N_Alumni WHERE N_Alumni.Client_ID = N_Client.Client_ID) AS Alumni, (SELECT COUNT(Client_ID) FROM N_XREF_Client_Activity WHERE N_XREF_Client_Activity.Client_ID = N_Client.Client_ID AND Activity_ID = 1) AS SandB, (SELECT BAH_EMP_NID FROM N_XREF_Client_Activity WHERE N_XREF_Client_Activity.Client_ID = N_Client.Client_ID AND Activity_ID = 1) AS SandBMailer, (SELECT N_Vw_Client_BAH_Contact.BAH_EMP_NID FROM N_Vw_Client_BAH_Contact WHERE Relationship_Type_Code = 'MM' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS MMEMPNID, dbo.N_CLIENT.SURNAME AS Client_Surname, dbo.N_CLIENT.FIRST_NAME, dbo.N_CLIENT.FIRST_NAME AS Client_FirstName, dbo.N_CLIENT.COMPANY_ID, dbo.N_CLIENT.DIVISION_ID, dbo.N_CLIENT.BUSINESS_UNIT_ID, dbo.N_COMPANY.GROUP_ID, dbo.N_CLIENT.COUNTRY, dbo.N_GROUP.GROUP_NAME, dbo.N_CLIENT.CLIENT_ID, (SELECT IsNull(N_Vw_Client_BAH_Contact.First_Name, '') + ' ' + IsNull(N_Vw_Client_BAH_Contact.Surname, '') FROM N_Vw_Client_BAH_Contact WHERE Relationship_Type_Code = 'PC' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS PCFullName, (SELECT N_Vw_Client_BAH_Contact.BAH_EMP_NID FROM N_Vw_Client_BAH_Contact WHERE Relationship_Type_Code = 'PC' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS PCEMPNID, (SELECT NMT_Practice_Code FROM N_Vw_Client_BAH_Contact WHERE Relationship_Type_Code = 'PC' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS NMT_Practice_Code, (SELECT NMT_Practice_Name FROM N_Vw_Client_BAH_Contact WHERE Relationship_Type_Code = 'PC' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS NMT_Practice_Name, #returnTable.AddlFullName, #returnTable.AddlEMPNID, #returnTable.FunctionID as Function_ID, #returnTable.FunctionName as Function_Name, (SELECT IsNull(N_Vw_Client_BAH_Contact.First_Name, '') + ' ' + IsNull(N_Vw_Client_BAH_Contact.Surname, '') FROM N_Vw_Client_BAH_Contact WHERE Relationship_Type_Code = 'CSO' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS CSOFullName, (SELECT N_Vw_Client_BAH_Contact.BAH_EMP_NID FROM N_Vw_Client_BAH_Contact WHERE Relationship_Type_Code = 'CSO' AND N_Vw_client_BAH_Contact.Client_ID = N_Client.Client_ID) AS CSOEMPNID, ISNULL(dbo.N_CLIENT.ARCHIVE_FLAG, 'N') AS Archive_Flag, dbo.N_COMPANY.TARGET_COMPANY_FLAG, dbo.N_COMPANY.INDUSTRY_ID, N_Client.Address1, N_Client.Address2, N_Client.Address3, N_Client.Address4, N_Client.Address5, N_Client.City, N_Client.State, N_Client.Postal_Code, N_Client.Country, N_Client.Region, N_Client.Office_Code, N_Client.Broderick_Target_Flag FROMdbo.N_CLIENT INNER JOIN dbo.N_COMPANY ON dbo.N_CLIENT.COMPANY_ID = dbo.N_COMPANY.COMPANY_ID LEFT OUTER JOIN dbo.N_GROUP ON dbo.N_COMPANY.GROUP_ID = dbo.N_GROUP.GROUP_ID LEFT OUTER JOIN dbo.N_REF_INDUSTRY ON dbo.N_COMPANY.INDUSTRY_ID = dbo.N_REF_INDUSTRY.INDUSTRY_ID LEFT OUTER JOIN dbo.N_DIVISION ON dbo.N_DIVISION.DIVISION_ID = dbo.N_CLIENT.DIVISION_ID LEFT OUTER JOIN #returnTable ON #returnTable.CLIENT_ID = dbo.N_CLIENT.CLIENT_ID LEFT OUTER JOIN dbo.N_BUSINESS_UNIT ON dbo.N_CLIENT.BUSINESS_UNIT_ID = dbo.N_BUSINESS_UNIT.BUSINESS_UNIT_ID ORDER BY N_Client.client_id Where upper(title) like '%parameter_value%' and company_id = 'parameter_value' and Nmt_practice_code = 'parameter_value' ...............and so on
What we would like to do is to add 15 (where some may be null) input parameters to the definition of the query and then somehow (where the parameter is not null), dynamically add that parameter to the WHERE clause of the query illustrated in italics above. The bold print are examples of 3 of the 15 parameters to be passed into the query by the proc, so basically title, company_id,Nmt_practice_code would be the 3 parameters being passed into this proc.
So in other words if 9 parameters out of the 15 are passed into the proc, we would like those 9 parameters to be added/built dynamically onto the SQL Query as 9 predicates. I hope I have been clear. Does anyone have any experience with this??? Help!!
This should be an easy enough answer to find if I just knew what to search on!
I am building a query within my stored procedure based on what parameters are passed in. For example, suppose I have a table with first name and last name. I can call the sp with either first name or last name or both, so I build a query accordingly that says: select * from tblNames where FirstName = 'Hannah' or select * from tblNames where LastName = 'Montana' or select * from tblNames where FirstName = 'Hannah' and LastName='Montana'
My problem is putting the single quotes around the variable value. SELECT @whereClause = @whereClause + ' AND tblNames.LastName=' @LastName-with-singlequotes-around-it
I have created SP in SQL 2K5 and make the where clause as parameter in the Sp. i am passing the where clause from my UI(ie ASP.NET), when i pass the where clause to SP i am not able to fetch the results as per the given criteria.
WhereClause from UI: whereClause="where DefectImpact='High'"
SQL Query in SP: SELECT @sql='select * from tablename'
Exec(@sql + @whereClause )
Here i am not able to get the results based on the search criteria. Instead i am getting all the results.
Can anyone tell me why the line highlighted in blue produces the following error when I try to run this stored proc? I know the parameters are set properly as I can see them when debugging the SP. I'm using this type of approach as my application is using the objectdatasource with paging. I have a similar SP that doesn't have the CategoryId and PersonTypeId parameters and that works fine so it is the addition of these new params that has messed up the building of the WHERE clause The Error is: "Syntax error converting the varchar value ' WHERE CategoryId = ' to a column of data type int." Thanks Neil CREATE PROCEDURE dbo.GetPersonsByCategoryAndTypeByName (@CategoryId int, @PersonTypeId int, @FirstName varchar(50)=NULL, @FamilyName varchar(50)=NULL, @StartRow int, @PageSize int) AS Declare @WhereClause varchar(2000)Declare @OrderByClause varchar(255)Declare @SelectClause varchar(2000) CREATE TABLE #tblPersons ( ID int IDENTITY PRIMARY KEY , PersonId int , TitleId int NULL , FirstName varchar (50) NULL , FamilyName varchar (50) NOT NULL , FullName varchar (120) NOT NULL , AltFamilyName varchar (50) NULL , Sex varchar (6) NULL , DateOfBirth datetime NULL , Age int NULL , DateOfDeath datetime NULL , CauseOfDeathId int NULL , Height int NULL , Weight int NULL , ABO varchar (3) NULL , RhD varchar (8) NULL , Comments varchar (2000) NULL , LocalIdNo varchar (20) NULL , NHSNo varchar (10) NULL , CHINo varchar (10) NULL , HospitalId int NULL , HospitalNo varchar (20) NULL , AltHospitalId int NULL , AltHospitalNo varchar (20) NULL , EthnicGroupId int NULL , CitizenshipId int NULL , NHSEntitlement bit NULL , HomePhoneNo varchar (12) NULL , WorkPhoneNo varchar (12) NULL , MobilePhoneNo varchar (12) NULL , CreatedBy varchar(40) NULL , DateCreated smalldatetime NULL , UpdatedBy varchar(40) NULL , DateLastUpdated smalldatetime NULL, UpdateId int ) SELECT @OrderByClause = ' ORDER BY FamilyName, FirstName' SELECT @WhereClause = ' WHERE CategoryId = ' + @CategoryId + ' AND PersonTypeId = ' + @PersonTypeIdIf NOT @Firstname IS NULLBEGIN SELECT @WhereClause = @WhereClause + ' AND FirstName LIKE ISNULL(''%'+ @FirstName + '%'','''')'ENDIf NOT @FamilyName IS NULLBEGIN SELECT @WhereClause = @WhereClause + ' AND (FamilyName LIKE ISNULL(''%'+ @FamilyName + '%'','''') OR AltFamilyName LIKE ISNULL(''%'+ @FamilyName + '%'',''''))'END Select @SelectClause = 'INSERT INTO #tblPersons( PersonId, TitleId, FirstName, FamilyName , FullName, AltFamilyName, Sex, DateOfBirth, Age, DateOfDeath, CauseOfDeathId, Height, Weight, ABO, RhD, Comments, LocalIdNo, NHSNo, CHINo, HospitalId, HospitalNo, AltHospitalId, AltHospitalNo, EthnicGroupId, CitizenshipId, NHSEntitlement, HomePhoneNo, WorkPhoneNo, MobilePhoneNo, CreatedBy, DateCreated, UpdatedBy, DateLastUpdated, UpdateId) SELECT PersonId, TitleId, FirstName, FamilyName , FullName, AltFamilyName, Sex, DateOfBirth, Age, DateOfDeath, CauseOfDeathId, Height, Weight, ABO, RhD, Comments, LocalIdNo, NHSNo, CHINo, HospitalId, HospitalNo, AltHospitalId, AltHospitalNo, EthnicGroupId, CitizenshipId, NHSEntitlement, HomePhoneNo, WorkPhoneNo, MobilePhoneNo, CreatedBy, DateCreated, UpdatedBy, DateLastUpdated, UpdateId FROM vw_GetPersonsByCategoryAndType ' EXEC (@SelectClause + @WhereClause +@OrderByClause)
I am new to SSAS. I have requirement to build a cube based on SQL Stored procedure. This Stored Procedure contains lot of temp tables, which are aggregated as measure columns.
Initially I have done creating views on each temp table, finally I created a view which calls like 15 views. when I try to execute the view, it is taking long time to execute the view.
I tried building cube on this view, when I try to deploy, even it is taking long time to deply..I have waited for 2 hours, still the deployement process going..
What I wonder is, is there any other way I can build cube based on SQL stored Procedure.
This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.
I ran across this technique being used in an application the other day. It seems not a good idea to me. What do you think?
1. The proc builds a basic query, nothing real fancy, into a string variable called @SQL defined as varchar 2000. Depending on the result desired, the group by clause can be one of three different sort orders.
2. The string is executed via EXEC @SQL.
It seems to me that the whole process can eliminate the EXEC and just use some other construct. All the parameters are passed in via the initial call to the stored proc. It also seems that every time this is executed it will result in a new query compile and cache useage, no matter what. Wasteful? Should I take the developers aside and knock heads? I think the app was coded by some folks who were rookies then but may be willing to crack open their code. Or, am I the one that is a rookie?
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?
I hope I can express what I need to know.Here it goes. I am planning on writing a single stored procedure to include two select statements and followed by a third like so:Select * etcUNION ALLSelect * etcGOSelect something elseMy question is this: does this return one record set (returning all records from the three Select statements) or two, the first being the result from the UNION and the second from the select following the GO. I will need to access the data returned from the resultant UNION and from the select after the Go, so I wonder if I'll be dealing with a single dataset or two. I hope this made sense.
I have a stored procedure using UNION joins on three SQL queries. Sadly, I'm only now learning how to use Stored Procedures, so if this is a really dumb question, please forgive me. I'm not used to big UNION statements like this either... usually I'm just programming websites to serve information out pretty simply :) I need to return one result set, sorted by date... one complete result per day. eg: 5/15/2007 | XX | XX | XX | XX | XX | XX |5/16/2007 | XX | XX | XX | XX | XX | XX |5/17/2007 | XX | XX | XX | XX | XX | XX | Currently, when I run the query, I'm getting three separate date values for each date... eg:5/15/2007 | XX | XX | 00 | 00 | 00 | 00 |5/15/2007 | 00 | 00 | XX | XX | 00 | 00 |5/15/2007 | 00 | 00 | 00 | 00 | XX | XX |5/16/2007 | XX | XX | 00 | 00 | 00 | 00 |5/16/2007 | 00 | 00 | XX | XX | 00 | 00 |5/16/2007 | 00 | 00 | 00 | 00 | XX | XX |etc How do I fix this? I've looked through my query ad naseum and don't see anything that sets me off as "wrong". Here is the stored procedure if you can help. I'd really really love the help!
C R E A T E P R O C E D U R E sp_ApptActivityDate (@strWHERE as varchar(500), @strWHERECANCELED as varchar(500)) as exec ('SELECT [date] AS Date, SUM(length) AS TotalSlots, COUNT(cast(substring(appointUniqueKey, 1, 1) AS decimal)) AS TotalAppts, SUM(length * 5) / 60 AS TotalSlotHours, 0 AS TotalActiveSlots, 0 AS TotalActiveAppts, 0 AS TotalActiveSlotHours, 0 AS totalCancelSlots, 0 AS TotalCancelAppts, 0 AS TotalCancelSlotHoursFROM dbo.vw_ALL_ApptActivity ' + @strWHERE + ' UNIONSELECT [date] as DATE, 0 AS TotalSlots, 0 AS TotalAppts, 0 AS TotalSlotHours, SUM(length) AS TotalActiveSlots, COUNT(cast(substring(appointuniquekey, 1, 1) AS decimal)) AS TotalActiveAppts, SUM(length * 5) / 60 AS TotalActiveSlotHours, 0 AS totalCancelSlots, 0 AS TotalCancelAppts, 0 AS TotalCancelSlotHoursFROM dbo.vw_Active_ApptActivity' + @strWHERE + ' UNIONSELECT [date] as DATE, 0 AS TotalSlots, 0 AS TotalAppts, 0 AS TotalSlotHours, 0 AS TotalActiveSlots, 0 AS TotalActiveAppts, 0 AS TotalActiveSlotHours, SUM(length) AS totalCancelSlots, COUNT(cast(substring(AppointUniqueKey, 1, 1) AS decimal)) AS TotalCancelAppts, SUM(length * 5) / 60 AS TotalCancelSlotHoursFROM dbo.vw_CANCELED_ApptActivity ' + @strWHERECANCELED + ' ORDER BY dbo.vw_ALL_ApptActivity.[Date] ' )GO
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'
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 have MSSQL 2005. On earlier versions of MSSQL saving a stored procedure wasn't a confusing action. However, every time I try to save my completed stored procedure (parsed successfully ) I'm prompted to save it as a query on the hard drive.
How do I cause the 'Save' action to add the new stored procedure to my database's list of stored procedures?
We recently upgraded to SQL Server 2005. We had several stored procedures in the master database and, rather than completely rewriting a lot of code, we just recreated these stored procedures in the new master database.
For some reason, some of these stored procedures are getting stored as "System Stored Procedures" rather than just as "Stored Procedures". Queries to sys.Objects and sys.Procedures shows that these procs are being saved with the is_ms_shipped field set to 1, even though they obviously were not shipped with the product.
I can't update the sys.Objects or sys.Procedures views in 2005.
What effect will this flag (is_ms_shipped = 1) have on my stored procedures?
Can I move these out of "System Stored Procedures" and into "Stored Procedures"?
I am writing a stored procedure that takes in a customer number, a current (most recent) sales order quote, a prior (to most current) sales order quote, a current item 1, and a prior item 1, all of these parameters are required.Then I have current item 2, prior item 2, current item 3, prior item 3, which are optional.
I added an IF to check for the value of current item 2, prior item 2, current item 3, prior item 3, if there are values, then variable tables are created and filled with data, then are retrieved. As it is, my stored procedure returns 3 sets of data when current item 1, prior item 1, current item 2, prior item 2, current item 3, prior item 3 are passed to it, and only one if 2, and 3 are omitted.I would like to learn how can I return this as a one data set, either using a full outer join, or a union all?I am including a copy of my stored procedure as it is.
I am writing a set of store procedures (around 30), most of them require the same basic logic to get an ID, I was thinking to add this logic into an stored procedure.
The question is: Would calling an stored procedure from within an stored procedure affect performance? I mean, would it need to create a separate db connection? am I better off copying and pasting the logic into all the store procedures (in terms of performance)?
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie exec dbo.DeriveStatusID 'Created' returns an int value as 1 (performed by "SELECT statusID FROM statusList WHERE statusName= 'Created') but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie: exec dbo.AddProduct_Insert 'widget1' which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) I want to simply the insert to perform (in one sproc): SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example). My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert). Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
i have created the folowing function but keep geting an error.
Only functions and extended stored procedures can be executed from within a function.
Why am i getting this error!
Create Function myDateAdd (@buildd nvarchar(4), @avdate as nvarchar(25)) Returns nvarchar(25) as Begin declare @ret nvarchar(25) declare @sqlval as nvarchar(3000)
set @sqlval = 'select ''@ret'' = max(realday) from ( select top '+ @buildd +' realday from v_caltable where realday >= '''+ @avdate +''' and prod = 1 )a'
