I have nested a Stored Procedure within a stored procedure. The nested stored procedure ends in a select statement. What I'd like to do is either capture the results of the select statement (it will be 1 row with 3 columns, I only need the contents of first column), or suppress the select statement from displaying in the final results of the Stored Procedure it is nested in.
Is there any way to do either of those?
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 need help nesting select statements. Here's what I'm trying to do:
select (select e.emp_name_lf as employee, e.emp_id
from employee e, install_payroll_detail ipd
where e.emp_id = ipd.emplno)
e.emp_name_lf as username
from employee e, install_payroll_master ipm
where e.emp_id = ipm.entered_by
I just want one row with both the employee and username, however I cannot get the syntax. Any help is greatly appreciated.
Thanks!
I'm just wodnering if you have two select statements, one nested inside another, can you reference tables from the outer loop?
for example, say I would like to find all employees who have at least two clients and employees and clients have a one to many relationship.
select *
from Employee e
where
(
select count(*)
from Clients c
where c.EmployeeId = e.EmployeeId
) >= 2
This obviously doesn't work - but how would i go about doing something like this?
So I have another query I can't seem to function the way I was hoping. I've learned a lot in this past month or so but I've hit another challenge. Anyways what I'm trying to do is when a user/student wants to add a new major I want to show a list of majors that are NOT already in his/her profile. So basically if I had a full list of majors:
Accounting
Computer Science
Mathematics
and the user already had Mathematics in his/her profile I'd like it to display only:
Accounting
Mathematics
Below is the layout of the tables, my attempt at the query, and then below that some example data.
Code Snippet
USE [C:COLLEGE ACADEMIC TRACKERCOLLEGE ACADEMIC TRACKERCOLLEGE.MDF]
GO
/****** Object: Table [dbo].[Majors] Script Date: 04/17/2008 22:38:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Majors](
[MajorID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_Majors] PRIMARY KEY CLUSTERED
(
[MajorID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
USE [C:COLLEGE ACADEMIC TRACKERCOLLEGE ACADEMIC TRACKERCOLLEGE.MDF]
GO
/****** Object: Table [dbo].[MajorDisciplines] Script Date: 04/17/2008 22:38:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MajorDisciplines](
[MajorDisciplineID] [int] IDENTITY(0,1) NOT NULL,
[DegreeID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MajorID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DisciplineName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Criteria] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_MajorDiscipline] PRIMARY KEY CLUSTERED
(
[MajorDisciplineID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MajorDisciplines] WITH CHECK ADD CONSTRAINT [FK_MajorDiscipline_DegreeID] FOREIGN KEY([DegreeID])
REFERENCES [dbo].[Degree] ([DegreeID])
GO
ALTER TABLE [dbo].[MajorDisciplines] CHECK CONSTRAINT [FK_MajorDiscipline_DegreeID]
GO
ALTER TABLE [dbo].[MajorDisciplines] WITH CHECK ADD CONSTRAINT [FK_MajorDiscipline_MajorID] FOREIGN KEY([MajorID])
REFERENCES [dbo].[Majors] ([MajorID])
GO
ALTER TABLE [dbo].[MajorDisciplines] CHECK CONSTRAINT [FK_MajorDiscipline_MajorID]
USE [C:COLLEGE ACADEMIC TRACKERCOLLEGE ACADEMIC TRACKERCOLLEGE.MDF]
GO
/****** Object: Table [dbo].[MajorDisciplines] Script Date: 04/17/2008 22:38:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MajorDisciplines](
[MajorDisciplineID] [int] IDENTITY(0,1) NOT NULL,
[DegreeID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MajorID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DisciplineName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Criteria] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_MajorDiscipline] PRIMARY KEY CLUSTERED
(
[MajorDisciplineID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MajorDisciplines] WITH CHECK ADD CONSTRAINT [FK_MajorDiscipline_DegreeID] FOREIGN KEY([DegreeID])
REFERENCES [dbo].[Degree] ([DegreeID])
GO
ALTER TABLE [dbo].[MajorDisciplines] CHECK CONSTRAINT [FK_MajorDiscipline_DegreeID]
GO
ALTER TABLE [dbo].[MajorDisciplines] WITH CHECK ADD CONSTRAINT [FK_MajorDiscipline_MajorID] FOREIGN KEY([MajorID])
REFERENCES [dbo].[Majors] ([MajorID])
GO
ALTER TABLE [dbo].[MajorDisciplines] CHECK CONSTRAINT [FK_MajorDiscipline_MajorID]
SELECT MajorID
FROM Majors majs
WHERE majs.MajorID NOT IN (SELECT majDis.MajorID FROM MajorDisciplines majDis WHERE majDis.MajorDisciplineID NOT IN (SELECT sMajors.MajorDisciplineID FROM Student_Majors sMajors WHERE sMajors.StudentID = 0))
dbo.Majors
MajorID
Accounting
Computer Science
Mathematics
dbo.MajorDisciplines
MajorDisciplinesID ... MajorID ...
1 Computer Science
2 Accounting
3 Accounting
4 Mathematics
dbo.Student_Majors
MajorDisciplineID StudentID
1 0
Oh also for the MajorIDs I don't want it to return duplicates such as the Accounting in MajorDisciplines...which I was hoping my query wouldn't do but it returns absolutely random data...
Hope someone can help!
I cannot seem to get nesting of stored procedures to work. I must have the syntax wrong. Should the following work:
SELECT *
FROM spWod_rptWoStatusSummary
Where spWod_rptWoStatusSummary is a Stored Procedure.
The above does not work. I get an error telling me that spWod_rptWoStatusSummary is an object that does not exit.
Does anyone know what the correct syntax is?
I cannot seem to get nesting of stored procedures to work. I must have the syntax wrong. Should the following work:
SELECT *
FROM spWod_rptWoStatusSummary
(Where spWod_rptWoStatusSummary is a Stored Procedure).
The above does not work. I get an error telling me that spWod_rptWoStatusSummary is an object that does not exit.
Does anyone know what the correct syntax is?
Hi Out There
The attempt to create the following stored procedure, which is supposed to call the previously created stored procedures inside it,
causes the following errors.
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@OfferIDTwo'.
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@OfferIDThree'.
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@OfferID'.
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@OfferID'.
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@OfferID'.
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@OfferID'.
Create Procedure spPrepareOfferSimulation
@OfferIDOne int
As
exec spPopulateOfferTables @OfferID
go
exec spPopulateOfferProduct @OfferID
go
exec spPopulateOfferDictionary @OfferID
go
exec spPopulateOfferCondition @OfferID
go
exec spPopulateOfferError @OfferID
go
exec spPopulateOfferLimit @OfferID
go
exec spPopulateOfferQA @OfferID
go
Can someone provide me with some productive input to fix this mess?
I have a caller stored procedure (sp_A) which calls several stored procedures (sp_1,sp_2,sp_3,sp_4, sp_5)
The statements in the called stored procedures sp_1 to sp_5 are all contained within BEGIN TRY -- BEGIN TRAN and BEGIN CATCH blocks which ensure that any errors in the SQL statements are rolled back when an error occurs.
However, I have noticed that when the called stored procedure fails, the caller procedure does not fail Rather it continues processing the remaining sps. I want to add code in my calling sp to stop this fom happening. Any ideas?
I have used this statement within my calling sp but no joy yet.
set @ErrCode = 0
exec @ErrCode = sp_executesql @Sql
if @ErrCode <> 0
begin
return 1
end
Any one with ideas? Thanks
I need the output of a stored procedure in another SP. That's simple (using a temporary table). But there's a small (big error ) problem.
Since I have to manually define the temporary table with it's fields and datatype to recieve the output from the nested SP , this approach would fail if, in the future more parameters are required to be returned . Is it possible to immunise my SP to such a consequence at creation time, rather them having to amend the temporary table later ?
Any idea how to overcome the above problem ? Is there a way that the temporary table can be automatically created like we have in a SQL statement with the INTO keyword. Any ideas ?
Thanks,
Alec
I have a stored procedure that returns a scalar value and I want to use that value (among other places) within another stored procedure.
-- The general purpose stored procedure is far too long to include here but I've included the last several lines of code at the bottom to show general gist of it and how it exits.--
The only way I can find to use that returned value "criteria" in a stored procedure is to define a temporary table, INSERT it into the table and then extract it from the temprary table into the variable where I actually wanted it.... i.e.
DECLARE @CriteriaTable TABLE ( Criteria VARCHAR(8000) )INSERT INTO @CriteriaTable (Criteria) EXEC psp_WRP_DisplayCriteria @UserID, 'Dealers, Prefix, Serial Range, Models, Makes, Sold Date', @UserGroup SELECT @Criteria=Criteria FROM @CriteriaTable
This seems like a ridiculously long winded and confusing way of doing things, especially since Im doing it in a dozen different procedures, half a dozen reports and 1 (so fasr) asp.net/VB web site - and I can't help thinking there must be a better way using just one or even zero extra lines of code to do this.
e.g. SELECT@Criteria = (EXEC dbo.psp etc...)
... or some variation thereof with the correct syntax.
or even better going to where that variable is used and changing ...
EXEC dbo.[psp_InsertWarrantyReportTracking]
@UserID = @UserID ,
@ReportName = 'rptChassisTrackExtdWarranty', -- <------
@ReportCriteria = @Criteria
to ...
EXEC dbo.[psp_InsertWarrantyReportTracking] @UserID = @UserID , @ReportName = 'rptChassisTrackExtdWarranty', -- <------ @ReportCriteria = (EXEC dbo.psp_ etc...)... or some variation thereof with the correct syntax.
But no matter how I try and how I search I can't find any way other than in what is otherwise a completely supefluous temporary table.
Am I missing something or is that REALLY the only way to get a hold of that returned value?
------ Last portion of the general purpose routine I'm trying to call ---------------------If Patindex('%RO Audit%',@Parmlist) > 0
Begin
set @Criteria = @Criteria + '- RO Audit date'
select @All=ROAuditAll, @From = ROAuditFrom, @To = ROAuditTo
from dbo.tblWRParameters where @UserID = UserName
If @All = 1
set @Criteria = @Criteria + '- ALL' + @NL
else
set @Criteria = @Criteria + 'is between ' + rtrim(@From) + ' and ' + rtrim(@to) + @NL
END
--Return the combined parameter field
select @Criteria as Criteria
END
Has anyone faced problems in calling one stored procedure from within another stored procedure ? I am considering using nested SPs, and would appreciate any inputs on it.
Thks,
SC
Hi,
This is driving me up the wall,
I have used SQL for donkey's years, but never really used Stored Procedures, well, I am starting to migrate functions from a front end ASP.Net system, and utilising the stored procedures.
I have a problem with something I thought would be really basic, i am trying to use IF THEN statements within the stored procedure to change the WHERE elements based on the parameters passed to it.
Here is the script (copied from the modification screen)
USE [QP]
GO
/****** Object: StoredProcedure [dbo].[USERS_LIST] Script Date: 02/21/2008 21:50:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[USERS_LIST]
@SearchVarChar(100)
As
SELECT TOP 100 PERCENT dbo.Members.EntryID, dbo.Members.EntryDate, dbo.Members.Username, dbo.Members.Forename, dbo.Members.Surname, dbo.Members.Gender,
dbo.Members.DateofBirth, dbo.Members.LastAction, dbo.Members.AdminUser, dbo.ActiveMember_Status.Status, dbo.ActiveMember_Mobile.Value AS Mobile,
dbo.ActiveMember_Email.Value AS Email, dbo.ActiveMember_Location.Location1, dbo.ActiveMember_Location.Location2, dbo.ActiveMember_Location.Location3,
dbo.ActiveMember_Location.Location4, dbo.F_AGE_IN_YEARS(dbo.members.dateofbirth, GetDate()) As Age
FROM dbo.Members INNER JOIN
dbo.ActiveMember_Status ON dbo.Members.EntryID = dbo.ActiveMember_Status.UserID LEFT OUTER JOIN
dbo.ActiveMember_Location ON dbo.Members.EntryID = dbo.ActiveMember_Location.UserID LEFT OUTER JOIN
dbo.ActiveMember_Email ON dbo.Members.EntryID = dbo.ActiveMember_Email.UserID LEFT OUTER JOIN
dbo.ActiveMember_Mobile ON dbo.Members.EntryID = dbo.ActiveMember_Mobile.UserID
IF (@Search='Dowle')
WHERE (dbo.Members.Username = @Search) OR
(dbo.Members.Forename = @Search) OR
(dbo.Members.Surname = @Search) OR
(dbo.ActiveMember_Mobile.Value = @Search) OR
(dbo.ActiveMember_Email.Value = @Search) OR
(dbo.ActiveMember_Location.Location1 = @Search) OR
(dbo.ActiveMember_Location.Location2 = @Search) OR
(dbo.ActiveMember_Location.Location3 = @Search) OR
(dbo.ActiveMember_Location.Location4 = @Search)
END IF
ORDER BY dbo.Members.Username
The bit I am trying to do above isn't real, but the same error appears every which way I try.
Msg 156, Level 15, State 1, Procedure USERS_LIST, Line 14
Incorrect syntax near the keyword 'WHERE'.
What am i doing wrong?
Thanks
David
For every trigger and stored procedure I have a try-catch that writes to an error_log table.
The problem is the inner error is not preserved, always get:
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
As seen below - though commented out:
I tried commiting any transactions - though I didn't create one.
I played with the
XACT_STATE though that was 0
My test case was last procedure has 1/0
Thanks,
Russ
-----------------------------------------------------------
Below is what I have
Step 1)
ALTER Trigger [trg_ActivityLogEntryReportsError] ON [dbo].[ActivityLog]
FOR INSERT AS
DECLARE @ActivityLogID int
,@AlertMessageTypeID int
,@comment nvarchar(max)
,@Error_Source nvarchar(max)
--- etc.
SELECT
@ActivityLogID = ActivityLogID
,@AlertMessageTypeID = AlertMessageTypeID
,@Comment = Comment
FROM INSERTED
BEGIN TRY
if @AlertMessageTypeID = 2 -- activity reported an error
begin
exec proc_CreateAlertLogEntry_forError
@ActivityLogID
,@Comment
update ActivityLog
set flgActivityChecked = 1
where @activityLogId = activityLogID
end
END TRY
BEGIN CATCH
select
@Error_Source = 'trg_ActivityLogEntryReportsError '
,@Error_Procedure = ERROR_Procedure()
--- etc.
INSERT INTO ERROR_LOG
( Error_Source
,Error_Procedure
,Error_Message
--- etc.
)
VALUES
(
@Error_Source
,@Error_Procedure
,@Error_Message
---etc.
,@Error_Comment )
-- if @@TRANCOUNT > 0
--begin
--commit
--end
END CATCH
Step 2)
/*
This will be called by a Trigger
*/
ALTER Procedure [dbo].[proc_CreateAlertLogEntry_forError]
(@ActivityLogID int
,@Comment nvarchar(max))
AS
Declare
@ProcessScheduleID int
,@ProcessID int
--,@comment nvarchar(max)
,@Error_Source nvarchar(max)
---etc
BEGIN TRY
insert into AlertLog
(
AlertMessageTypeID
,comment
,ActivityLogID
)
values
(
2 -- error
,@comment
,@ActivityLogID
)
end
END TRY
BEGIN CATCH
PRINT 'ERROR OCCURED'
PRINT ERROR_Procedure() + ' ' + ERROR_MESSAGE()
select
@Error_Source = 'proc_CreateAlertLogEntry_forError '
---etc.
INSERT INTO ERROR_LOG
( Error_Source
,Error_Procedure
,Error_Message
---etc.
)
VALUES
(
@Error_Source
,@Error_Procedure
,@Error_Message
--- etc.)
-- if @@TRANCOUNT > 0
--begin
--commit
--end
END CATCH
update ActivityLog
set
flgActivityChecked = 1
,UpdatedDate = getdate()
,UpdatedBy = suser_sname()
where
ActivityLogID = @ActivityLogID
STEP 3
ALTER Trigger [trg_AlertLogEntry_SendsOnInsert] ON [dbo].[AlertLog]
FOR INSERT AS
declare
@AlertLogID Int
,@AlertMessageTypeID int
,@Comment nvarchar(max)
,@Error_Source nvarchar(max)
,@Error_Procedure nvarchar(max)
,@Error_Message nvarchar(max)
--- etc.
SELECT
@AlertLogID = AlertLogID
,@AlertMessageTypeID = AlertMessageTypeID
,@Comment = isnull(Comment,'')
,@ActivityLogID = isnull(ActivityLogID,-1)
FROM INSERTED
BEGIN TRY
PRINT 'trg_AlertLogEntry_SendsOnInsert'
PRINT @COMMENT
exec proc_SendEmail
@AlertLogID
,@AlertMessageTypeID
,@comment
,@ActivityLogID
END TRY
BEGIN CATCH
select
@Error_Source = 'trg_AlertLogEntry_SendsOnInsert '
,@Error_Procedure = ERROR_Procedure()
,@Error_Message = ERROR_MESSAGE()
--- etc.
INSERT INTO ERROR_LOG
( Error_Source
,Error_Procedure
-- etc.)
VALUES
(
@Error_Source
,@Error_Procedure
,@Error_Message
---etc.)
-- if @@TRANCOUNT > 0
--begin
--commit
--end
END CATCH
STEP 4
ALTER Procedure [dbo].[proc_SendEmail]
(
@AlertLogID Int
,@AlertMessageTypeID int
,@Comment nvarchar(max) = ''
,@ActivityLogID int = -1
)
AS
declare @AlertSubject nvarchar(512)
,@AlertBody nvarchar(max)
,@myQuery nvarchar(512)
,@profile_name1 nvarchar(128)
,@return_value int
,@mymailitem int
,@Error_Source nvarchar(max)
---etc.
,@Error_Comment nvarchar(max)
,@Test int
/*
@return_value int -- not using at this point but 0 is OK 1 is failure
@mymailitem int -- not using now could store mailitem_id which is on msdb.dbo.sysmail_mailitems
sysmail_mailitems.sent_status could be either 0 new, not sent, 1 sent, 2 failure or 3 retry.
*/
select top 1 @profile_name1 = [name] from msdb.dbo.sysmail_profile
order by profile_id
set @profile_name1 = rtrim(ltrim(@profile_name1))
print '@profile_name1: ' + @profile_name1
print '@comment: ' + @comment
Declare @CrsrRecipient Cursor
BEGIN TRY
PRINT 'proc_SendEmail'
--set @test = 1/0 'test crashing
select
@AlertSubject = 'AlertSubject'
,@AlertBody = 'AlertBody'
,@recipients = 'russ@test.com'
print 'sending email ' + CAST(getdate() as nvarchar(100))
EXEC @return_value = msdb.dbo.sp_send_dbmail
@profile_name = @profile_name1
,@recipients = @EMAILID
,@body = @AlertBody
,@subject = @AlertSubject
,@mailitem_id = @mymailitem OUTPUT
print 'Done ' + CAST(getdate() as nvarchar(100))
print cast(@return_value as nvarchar(100))
update alertlog
set AlertSendStatusID = 1 --sent
where
@AlertLogID = AlertLogID
END TRY
BEGIN CATCH
PRINT 'ERROR OCCURED'
PRINT ERROR_Procedure() + ' ' + ERROR_MESSAGE()
select
@Error_Source = ' proc_SendEmail '
,@Error_Procedure = ERROR_Procedure()
--- etc.
INSERT INTO ERROR_LOG
( Error_Source
,Error_Procedure
---etc.)
VALUES
(
@Error_Source
,@Error_Procedure
,@Error_Message
--- etc.)
update alertlog
set AlertSendStatusID = 2 --error
where
@AlertLogID = AlertLogID
--if @@TRANCOUNT > 0
--begin
--commit
--end
END CATCH
Hi,We're having a big discussion with a customer about where to store the SQL and DML statements. (We're talking about SQL Server 2000)We're convinced that having all statements in the code (data access layer) is a good manner, because all logic is in the "same place" and it's easier to debug. Also you can only have more problems in the deployment if you use the stored procedures. The customer says they want everything in seperate stored procedures because "they always did it that way". What i mean by using seperate stored procedures is:- Creating a stored procedure for each DML operation and for each table (Insert, update or delete)- It should accept a parameter for each column of the table you want to manipulate (delete statement: id only)- The body contains a DML statement that uses the parameters- In code you use the name of the stored procedure instead of the statement, and the parameters remain... (we are using microsoft's enterprise library for data access btw)For select statements they think our approach is best...I know stored procedures are compiled and thus should be faster, but I guess that is not a good argument as it is a for an ASP.NET application and you would not notice any difference in terms of speed anyway. We are not anti-stored-procedures, eg for large operations on a lot of records they probably will be a lot better.Anyone knows what other pro's are related to stored procedures? Or to our way? Please tell me what you think...Thanks
View 1 Replies View RelatedIs it true that replacing embedded sql statements (that insert/update and fetch data
from sql databases) by stored procedures improves overall respones time and SQL performance.
We have tons of embedded SQL statements in our Visual Basic modules and are debating whether replacing them by SQL Stored procedures
will really be worht the effort.
Any insights on this?
Thanks in advance. If possible, please reply to my e-mail.Thanks.
i would like some conditional settings in a stored procedure
i have a variable
@Variable
and I want to do a conditional statement like
if @Variable = 1 then @Variable2 = 1
Elseif @Variable = 3 then @Variable2 = 4
Else @Variable = 11 then @Variable2 = 12
not sure about how to implement elseif bit
i know you can have
{if this Else That} in T-Sql
trying to get to the bottom of this for some time...... eventually tobe used with asp.heres the problemthe following rather complex SQL statement works fine via queryanalyser:SELECT TOP 100 tbl_LevelDetail.nvchLevelName AS DataLevelName,MAX(CASE tintDataFieldId WHEN '1' THEN CAST(nvchData AS int) ELSE 0END) AS 'Pos',MAX(CASE tintDataFieldId WHEN '2' THEN CAST(nvchData AS char) ELSE '' END) AS 'AreaName',MAX(CASE tintDataFieldId WHEN '3' THEN CAST(nvchData AS char) ELSE '' END) AS 'BDGName',MAX(CASE tintDataFieldId WHEN '4' THEN CAST(nvchData AS char) else '' END) AS 'Performance',MAX(CASE tintDataFieldId WHEN '5' THEN CAST(nvchData AS int) ELSE 0END) AS 'Qualifier'FROM tbl_Data, tbl_Levels, tbl_LevelDetail, tbl_LevelDetail AStbl_LevelDetail_ReportWHERE tbl_Data.nvchIncentiveId = 'MPW' AND tbl_Data.nvchPeriodId ='W27P'AND tbl_Levels.nvchIncentiveId = 'MPW' ANDtbl_LevelDetail.nvchIncentiveId = 'MPW'AND tbl_LevelDetail_Report.nvchIncentiveId = 'MPW' ANDtbl_Data.nvchDataLevelId = tbl_Levels.nvchDataLevelIdAND tbl_Levels.nvchDataLevelId = tbl_LevelDetail.nvchLevelIdAND tbl_Levels.nvchReportingLevelId =tbl_LevelDetail_Report.nvchLevelIdAND tbl_LevelDetail.nvchLevelTypeId = 2AND tbl_LevelDetail_Report.nvchLevelTypeId = 1AND tbl_Levels.nvchReportingLevelId IN ('a')GROUP BY tbl_Levels.nvchReportingLevelId, tbl_Levels.nvchDataLevelId,tbl_LevelDetail.nvchLevelName, tbl_LevelDetail_Report.nvchLevelNameORDER BY Pos, DataLevelNamereturns rows ok no problembut when trying to convert to a stored procedure i dont get anyresults:CREATE PROCEDURE usp_incmpwfilter_rs(@strPeriodID varchar ,@intLevelDetailID varchar,@intLevelReportID varchar,@strFilters varchar)ASset nocount onSELECT TOP 100 tbl_LevelDetail.nvchLevelName AS DataLevelName,MAX(CASE tintDataFieldId WHEN '1' THEN CAST(nvchData AS int) ELSE 0END) AS 'Pos',MAX(CASE tintDataFieldId WHEN '2' THEN CAST(nvchData AS char) ELSE '' END) AS 'AreaName',MAX(CASE tintDataFieldId WHEN '3' THEN CAST(nvchData AS char) ELSE '' END) AS 'BDGName',MAX(CASE tintDataFieldId WHEN '4' THEN CAST(nvchData AS char) else '' END) AS 'Performance',MAX(CASE tintDataFieldId WHEN '5' THEN CAST(nvchData AS int) ELSE 0END) AS 'Qualifier'FROM tbl_Data, tbl_Levels, tbl_LevelDetail, tbl_LevelDetail AStbl_LevelDetail_ReportWHERE tbl_Data.nvchIncentiveId = 'MPW' AND tbl_Data.nvchPeriodId =@strPeriodIDAND tbl_Levels.nvchIncentiveId = 'MPW' ANDtbl_LevelDetail.nvchIncentiveId = 'MPW'AND tbl_LevelDetail_Report.nvchIncentiveId = 'MPW' ANDtbl_Data.nvchDataLevelId = tbl_Levels.nvchDataLevelIdAND tbl_Levels.nvchDataLevelId = tbl_LevelDetail.nvchLevelIdAND tbl_Levels.nvchReportingLevelId =tbl_LevelDetail_Report.nvchLevelIdAND tbl_LevelDetail.nvchLevelTypeId = @intLevelDetailIDAND tbl_LevelDetail_Report.nvchLevelTypeId = @intLevelReportIDAND tbl_Levels.nvchReportingLevelId IN (@strFilters )GROUP BY tbl_Levels.nvchReportingLevelId, tbl_Levels.nvchDataLevelId,tbl_LevelDetail.nvchLevelName, tbl_LevelDetail_Report.nvchLevelNameORDER BY Pos, DataLevelNamethen call it by SQL statement:EXEC usp_incmpwfilter_rs 'W27P',2,1,'a'Returns no rows. This is the initial problem. Also there will beanother issue if i can get the above to work: the @strFilters cancontain multiple data, ie 'a','k'this works fine in the 1st sql statement ie: ANDtbl_Levels.nvchReportingLevelId IN ('a','k') but I dont know how topass as a parameter to the stored procedure. I cannot create temporarytables.i had not created the intial SQL statement, i am just trying toconvert it to a stored procedure which accepts thos parameters. thishas been a real headache for me, any help as always appreciatedgreatly.
View 3 Replies View RelatedHi,
I am doing some research on sqljdbc 1.2. I have run into a few problems with the jdbc driver. I think it would be so much easier just to ask the expert here.
1. when to use CallableStatment, PreparedStatement, Statement for stored proc and why?
http://msdn2.microsoft.com/en-us/library/ms378046.aspx
talks about using Statements with Stored Procedures.
It says for stored Proc with no param, use Statement; for stored proc with only input param, use PreparedStatement; and for stored proc with output param, return status or update count, use CallableStatement.
Is there any special considerations to select which one to use, is there any performance differences? I assume we can always use CallableStatement for stored proc.
2. mutliple statements on one Connection
The JDBC spec allows multiple Statement on one Connection with no need to close the previous Statment. When Connection.close() is called, all the Statements and ResultSets will be closed too.
I tried with regular stored proc, it is fine of have a CallableStatement, then a Statement, without close the CallableStatment, all the Statements are executed with no error.
However, when I change the stored proc, using explicit transaction, i.e BEGIN TRANSACTION, and COMMIT TRANSACTION, the Statement failed and throw an error:
com.microsoft.sqlserver.jdbc.SQLServerException: Server failed to resume the transaction, desc: 3500000001.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(Unknown Source)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(Unknown Source)
When I add a CallableStatement.close() before the create the second Statment, then it worked. Is this a bug or something special needs to be set for stored proc with Transaction?
3. Statement.getGeneratedKeys
Under the hood, is it making another round trip to get the generatedkeys, or when the Statement created with Statement.RETURN_GENERATED_KEYS is excuted, the identity field is already retured with the Statement?
This method call works with regular insert Statement, but does not work for PreparedStatement calling a stored proc. Is this a bug?
Thanks in advance,
jessica
We are migrating a SQL 6.5 application with 1900 stored procedures that use 100's of temp tables to SQL 2000.
A problem we have encountered was that we started out getting an "invalid column" errors on certain procedures. Investigation determined that the error was being generated in a nested procedure. The table that caused the error ended up being a temp table that was created using "select into". The following select statement from that temp table gave the invalid column error.
First thinking it was the "Select Into" we then discovered that the outer most procedure had created a temp table of the same name prior to executing the lower level procedure. After the select into, the next statement was a SELECT that went against what it thought was the inner temp table. However, it grabbed the outermost temp table and then couldn't find the appropriate columns and generated the error.
The solution, of course, was to rename the inner most temp table. We also remove the "select into" in the procedure by explicitly creating the temp table.
We tried creating some test procedures to attempt to reproduce this scenario without complete success.
Our test created 3 procedures (sp1 calling sp2 calling sp3) to mimic the current scenario. Sp1 created a temp table and executed sp2, which executed sp3. Sp3 created another temp table using the same name as the one created in sp1.
If we create all three procedures at the same time, it doesn't matter if we change the order in which they are created or whether we create the inner temp table explicitly or with a "select into", SQL Query Analyzer won't let us create the procedure because it finds that the temp table has been declared twice. If we create the procedures separately however, they compile and allow sp3 to create a temp table by the same name as sp1. After creating the procedures independently, they runs properly in all cases with everything in proper scope and no problems.
Admittedly, this is bad coding to start with, but what is happening with the scope of the temp tables within the stored procedures?
Thanks,
Glen Smith
Hi,I'm adapting access queries to sql server and I have difficulties withthe following pattern :query1 : SELECT * FROM Query2 WHERE A=@param1query 2: SELECT * FROM Table2 WHERE B=@param2The queries are nested, and they both use parameters.In MS Acccess the management of nested queries with parameters is soeasy (implicit declaration of parameters, transmission of parametersfrom main query to nested query)that I don't know what the syntax should be for stored procedures.The corresponding stored procedure would be something likeCREATE TABLE #TempTable (...table definition...)INSERT INTO #TempTable ExecProc spQuery2 @Param2SELECT * FROM #TempTable WHERE A=@Param1And spQuery2 would be : SELECT * FROM Table2 WHERE B=@ParamI was wondering if this syntax would work and if I can skip theexplicit declaration of #TempTable definition.Thanks for your suggestions.
View 5 Replies View RelatedHi there,I have a data manipulation process written in a Nested Stored procedurethat have four levels deeper. When I run these individual proceduresindividually they all seems to be fine. Where as when I run them alltogether as Nested proces (calling one in another as sub-procedures) Logfile is growing pretty bad like 25 to 30GB.. and finally getting kickedafter running disk space. This process is running around 3hrs on a SQLserever Standard Box having dual processer and 2gb ram.This procedures have bunch of bulk updates and at least one cursor ineacch procedure that gets looped through.I was wondering if anybody experienced this situation or have any clueas to why is this happening and how to resolve this?I am in a pretty bad shape to deliver this product and in need of urgenthelp.Any ideas would be greatly appreciated..Thanks in advance*** Sent via Developersdex http://www.developersdex.com ***
View 1 Replies View RelatedHi all,
I had developed all my reports using CRXI and stored procedures of MSSQL 2000. Now I am migrating from CRXI to SSRS. But i have a problem because of my report stored procedures. These stored procedures are complex and nested. When i try to use procedures in a report, it gives a error. When i comment out my EXEC line in my procedures, it goes OK.
Please help me to do this ?
Hai,
I just waana know whether we can use "FOR statement" in Stored Procedures. If yes, can you describe it with some examples of how they are used in stored procedures ?
Consider a situation where a stored procedure taking a varbinary(max) (BLOB) input parameter then calls a nested stored procedure and passes along that varbinary(max) as an input parameter to the nested stored procedure.
Is a copy of the BLOB provided to the nested stored procedure (passed by value) OR is the BLOB passed by reference.
My interest is in understanding the potential memory hit when handling large BLOBs in this environment.
For example, if the BLOB is 200MB, will SQL server need to allocate memory for a new copy each time it's passed to another stored procedure?
Looks like table type parameters are passed by reference, but I haven't been able to find any info on BLOBS in this context.
Hi,
I would like to use a stroed procedure within another stored procedure ( nested sp )
in a SQL project in VS.NET 2005. Since I have to use "context connection = true" as
connection string, I wont be able to use this connection for another sqlconnection
object. Because its already open. and If i try to use a regular connection string
("server=localhost;...") it will raise a security permission error. Having this
problem, Im not able to use nested stored procedures. Would anyone please give me a
hint how to resolve this issue?
Hello,
Is it possible to execute DBCC statements remotely in destination servers through stored procedures existing in source server.
I don’t want to use isql, osql, openquery, openresultset because I have to pass user id or password.
I have linked the required SQL servers using Linked servers.
If so please give some example.
Thanks in Advance,
Sateesh
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 want to know if it's possible or not..
Hi
I have few print statements in a stored procedure that gets called from the SSIS package. How do I make sure that these are captured in the SSIS log meaning how do I get them to be displayed in the Package Explorer window when running from the Business Intelligence Studio IDE.
I need to list out all the procedures which have select queries and does not use NOLOCK hint. Please note that there are more than 2000 sps and some sps have single select some have multiple, some does not have select queries at all. I need to find out only the ones which does not use NOLOCK hint in a select query.
View 8 Replies View RelatedHi 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?
Hi all,
I have 2 select statements in my Stored Proc.
I want to display the results of each query in my DataGridView.
However, only the data of the last select query is returned.
Why is this?
Thanks.
I have created a stored procedure with multiple select statements using MSSQL 2000. When I connect to this using a SQLDataSource it only seems to get the first SELECT. I need to access the multiple tables returned by the stoped procedure. Can anyone point me in the dirrection of how to do this.ThanksClearz
View 3 Replies View Related