Stored Procedure And Join Statement.

Mar 2, 2007

I'm very new to SQL and have been assigned to modify an existing Store Procedure. The existing is pasted below:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


ALTER PROCEDURE RPT_ACTIVITY_LEDGER
(
@StartDate datetime = NULL,
@EndDate datetime = NULL,
@PrjName varchar(50) = '%',
@TaskName varchar(50) = '%',
@EmpName varchar(50) = '%'
)
AS

SELECT ActivityCode AS 'Activity Code',
ActivityDate AS 'Activity Date',
ActDescription AS 'Activity Desc',
ActDetail AS 'Activity Detail',
ActSource AS 'Activity Source',
ActPrjSource AS 'Activity Type',
ActBillable AS 'Billable',
ActBillRate AS 'Bill Rate',
ActBilltoCCName AS 'Bill to Cost Center',
(CASE WHEN ActARClosed = 1 Then 'Billed' Else 'Not Billed' END) AS 'Billed Status',
ActBillingType AS 'Billing Type',
ActBudType AS 'Budget Type',
ActBudCat AS 'Budget Cat',
ActBudSubCat AS 'Budget Sub Cat',
ActCompMonth AS 'Company Month',
ActCompWeek AS 'Company Week',
ActCompYear AS 'Company Year',
ActCostRate AS 'Cost Rate',
ActCustName AS 'Customer',
ActCustMngr AS 'Customer Manager',
ActCustomerPct AS 'Customer Percentage',
ActCustPO AS 'Customer PO',
ActDataSplash AS 'Data Splash',
ActDocId AS 'Document ID',
ActEmpName AS 'Employee',
ActEmpCCCode AS 'Employee CC Code',
ActEmpCCGroup AS 'Employee CC Group',
ActEmpCCName AS 'Employee Cost Center',
ActEmpCCGrp3 AS 'Employee Department',
ActEmpCCGrp2 AS 'Employee Division',
ActEmpFTE AS 'Employee FTE',
ActEmpMgr AS 'Employee Manager',
ActEmpPos AS 'Employee Position',
ActEMpTaskName AS 'Employee Task',
(CASE WHEN ActFromType = 'Expense Report' THEN ActMoneyValue
WHEN ActFromType = 'Purchase Request' THEN ActMoneyValue
ELSE 0 END) AS 'Expenses' ,
ActFromType AS 'Form Type',
ActGroups AS 'Groups',
(CASE WHEN ActARClosed = 1 THEN ActBillTotal ELSE 0 END) AS 'Income',
ActInvoiceNum AS 'Invoice Number',
(CASE WHEN ActAPClosed = 1 Then 'Paid' Else 'Not Paid' END) AS 'Paid Status',
ActPrjCCName AS 'Project Cost Center',
ActPrjCCCode AS 'Project CC Code',
ActPrjCode AS 'Project Code',
ActPrjCCGrp3 AS 'Project Department',
ActPrjSource as 'Project Source',
ActPrjIndustry AS 'Project System',
ActPrjMgr AS 'Project Manager',
ActPrjName AS 'Project Name',
ActPrjPhase AS 'Project Phase',
ActPrjSponsor AS 'Project Sponsor',
ActPrjType AS 'Project Type',
ActTaskPhase AS 'Task Phase',
(CASE WHEN ActFromType = 'Time Card' THEN ActTimeValue ELSE 0 END) AS 'Time',
ActWeekEndDate AS 'Week Ending Date',
ActPersFlowTxt1 AS 'Person Custom Text 1',
ActPersFlowTxt2 AS 'Person Custom Text 2',
ActPersFlowTxt3 AS 'Person Custom Text 3',
ActProjFlowTxt1 AS 'Project Custom Text 1',
ActProjFlowTxt2 AS 'Project Custom Text 2',
ActProjFlowTxt3 AS 'Project Custom Text 3',
ActHeaderCustom1 as 'Header Custom Text 1',
ActHeaderCustom2 as 'Header Custom Text 2',
ActHeaderCustom3 as 'Header Custom Text 3',
ActHeaderCustom4 as 'Header Custom Text 4',
ActDetailCustom1 as 'Detail Custom Text 1',
ActDetailCustom2 as 'Detail Custom Text 2',
ActDetailCustom3 as 'Detail Custom Text 3',
ActDetailCustom4 as 'Detail Custom Text 4'


FROM ACTIVITIES
WHERE ActivityDate BETWEEN @StartDate AND @EndDate
AND (ISNULL(ActPrjName,'') LIKE @PrjName )
AND (ISNULL(ActEMpTaskName,'') LIKE @TaskName )
AND (ISNULL(ActEmpName,'') LIKE @EmpName )

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

*************

The goal is to add another table called EMPLOYEES with fields E1 and E2 as part of the returned data set. I need a join statement but I can't figure out the syntax that will actually pull the data. Any help would be appreciated. Thanks.

View 9 Replies


ADVERTISEMENT

SQL Server 2012 :: Stored Procedure - How To Join Another Table Into Select Statement

Jan 7, 2014

I have a stored procedure that I have written that manipulates date fields in order to produce certain reports. I would like to add a column in the dataset that will be a join from another table (the table name is Periods).

The structure of the periods table is as follows:

[ID] [int] NOT NULL,
[Period] [int] NULL,
[Quarter] [int] NULL,
[Year] [int] NULL,
[PeriodStarts] [date] NULL,
[PeriodEnds] [date] NULL

The stored procedure is currently:

USE [International_Forecast_New]
GO
/****** Object: StoredProcedure [dbo].[GetOpenResult] Script Date: 01/07/2014 11:41:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

[Code] ....

What I need is to add the period, quarter and year to the dataset based on the "Store_Open" value.

For example Period 2 looks like this
Period Quarter Year Period Start Period End
2 1 20142014-01-27 2014-02-23

So if the store_open value is 02/05/2014, it would populate Period 2, Quarter 1, Year 2014.

View 1 Replies View Related

T-SQL (SS2K8) :: One Stored Procedure Return Data (select Statement) Into Another Stored Procedure

Nov 14, 2014

I am new to work on Sql server,

I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.

Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.

View 1 Replies View Related

Stored Procedure And INNER JOIN

Sep 26, 2007

Will you please look at my stored procedure below? I originally had table "Servee" as Parent table and "Orders" talbe as child table, using relationships in VWD express. I am querying sql server 2005 express. When I run the page, I got an error saying a conflict  relationship Orders_FK-Servee. So I deleted the table relationship and ran the page, but the procdure didn't do anything, but no error.
 What I'm trying to do, is Insert a new record if the current (meaning displayed on the page) ServeeID is not present in the Orders table. If the current ServeeID is present, UPDATE the current record. Thanks in advance. 
 ALTER PROCEDURE dbo.newjob
@ServeeName varchar(500),@Identity int OUT
 
 
AS
IF NOT EXISTS(SELECT * FROM Servee INNER JOIN Orders ON Servee.ServeeID=Orders.ServeeID)
BEGININSERT Servee (ServeeName)
VALUES (@ServeeName)
END
ELSE
BEGIN
UPDATE Servee SET Servee.ServeeName=Servee.ServeeName FROM Servee INNER JOIN Orders ON Servee.ServeeID=Orders.ServeeID
ENDSET @Identity = SCOPE_IDENTITY()
 
 
Return

View 13 Replies View Related

Self Join Or Stored Procedure

Aug 3, 2005

hi gurushave a employee table which consist of the normal employee relatedfields which the fields empid and supervisor mainly related to thisquestion.have to create a single (the question is whether it is possible) or astored procedure to get the result as follow. the input parameter isempid.have to retrieve the subordinates records of the employee id passed asparameter and the drill down.for example from the below table. if empid 12 is been passed as theparameter value then it should retrieve the records with followingempid - 1, 26. the employee with empid 1 has subordinates under himwhich should also be pulled that is 16, 62 and so on. the output forthe above example will be a result with records in the following order- 12, 1,16,62, 26 and so on.hope i have explained the problem clearlywould appreciate if some one can guide in creating an efficient selfjoin or a stored procedureEmpId FirstName LastName Supervisor----- --------- -------- ----------1 Carl Hogans 1212 Fred Smith NULL16 Sue Bankers 126 Frank Green 1255 Karen Feeders NULL56 James Black 5557 Kirk Simmons 5658 Cliff Page 5659 Jimmy Plant 5660 Jack Cale 5961 Robert Santana NULL62 Jack Russell 1where clause or parameter passed is 'EmpId'thanx in advancebala

View 4 Replies View Related

SQL Stored Procedure Join With Parameters

Oct 31, 2006

I was just curious if there was anyway possible to accomplish something simliar to:

SELECT * FROM [dbo].[PRODUCT]
JOIN [dbo].[GetRegionProductPrice](@RegionClassID,[dbo].[PRODUCT].[ID]) Pricing ON Pricing.[ProductID] = [Product].[ID]
WHERE [CategoryID] = @ProductNavigationID

Basically what i am trying to do is Select all my products and obtain a product price for reach, which uses a complex stored procedure that obtains a price for a single product. I understand that i could create a cursor to go through it, but i was just curious if there was a better method... Thanks for your time..

-Devin

View 4 Replies View Related

Join Stored Procedure And View

Jun 22, 2005

   Hi everybody

View 3 Replies View Related

Stored Procedure - Select Distinct And Inner Join

Nov 16, 2006

I've been trying to get this to work right.
The db table has 3 fields: id, vwr, and reqType.
I need all DISTINCT vwr's from the table. (vwr's can repeat)
This gives me all rows, not distinct...
 select distinct d.id, d.vwr, d.reqType from tblVWR AS dinner join tblVWR ton d.vwr = t.vwr
Any suggestions?
Thanks,
Zath

View 2 Replies View Related

Question On Adding A Join To The Stored Procedure

Nov 15, 2007

The stored procedure below was working fine and I have added a inner join to it and it stopped working. I have highlighted the new code I have added to the stored procedure in red. Any suggestions on how to solve this issue?

I am getting the below error
Server: Msg 209, Level 16, State 1, Procedure AIG_GetRECON_TRANSACTION, Line 53
Ambiguous column name 'REINS_TYPE_CD'.

below is the query i changed.


ALTER PROCEDURE [dbo].[AIG_GetRECON_TRANSACTION]
@RECON_TRNSCTN_ID int=NULL,
@RECON_ITEM_ID int=NULL,
@DIVISION_ID int=NULL,
@REINS_TYPE_CD int=NULL,
@TRANSACTION_NO char(10)=NULL,
@TRANSACTION_NAME varchar(100)=NULL,
@REF_NUMBER varchar(20)=NULL,
@POLICY_CRS_REF_NO varchar(20)=NULL,
@GL_DTFrom datetime=NULL,
@GL_DTTo datetime=NULL,
@TRANSACTION_CD int=NULL,
@AMOUNT money=NULL,
@FILE_STATUS_CD int=NULL,
@TRNSCTN_OWR_CD int=NULL,
@ISSUE_CD int=NULL,
@SUPP_ISSUE_CD int=NULL,
@IRC_CLASSIFICATION_CD int=NULL,
@UNDER_90_DAYS money=NULL,
@OVER_90_DAYS money=NULL,
@AGING_DAYS_CNT int=NULL,
@VOCHER_NO varchar(50)=NULL,
@LOADED_DTFrom datetime=NULL,
@LOADED_DTTo datetime=NULL,
@SPUsageMode TINYINT = 0 -- This should be the last parameter

AS

-------------------------------------------------------------------------------
-- SP Usage Audit Info -- DO NOT REMOVE
-- All Stored Procedure code MUST be placed between Section 1 and 2 of
-- SP Usage Audit code
-------------------------------------------------------------------------------
-- SP Usaged Section 1 - Declare
-------------------------------------------------------------------------------
DECLARE @SPStartTime DATETIME
SELECT @SPStartTime = GETDATE()
DECLARE @SPEndTime DATETIME
DECLARE @AuditCount INT
-------------------------------------------------------------------------------

SELECT @GL_DTFROM = ISNULL(@GL_DTFROM, CONVERT(DATETIME,'1/1/1900'))
SELECT @GL_DTTO = ISNULL(@GL_DTTO, CONVERT(DATETIME,'12/31/9999'))
SELECT @LOADED_DTFROM = ISNULL(@LOADED_DTFROM, CONVERT(DATETIME,'1/1/1900'))
SELECT @LOADED_DTTO = ISNULL(@LOADED_DTTO, CONVERT(DATETIME,'12/31/9999'))

Begin

SELECT
RECON_TRNSCTN_ID,
RECON_ITEM_ID,
DIVISION_ID,
REINS_TYPE_CD,
TRANSACTION_NO,
TRANSACTION_NAME,
REF_NUMBER,
POLICY_CRS_REF_NO,
GL_DT,
TRANSACTION_CD,
AMOUNT,
FILE_STATUS_CD,
TRNSCTN_OWR_CD,
ISSUE_CD,
SUPP_ISSUE_CD,
IRC_CLASSIFICATION_CD,
UNDER_90_DAYS,
OVER_90_DAYS,
AGING_DAYS_CNT,
VOCHER_NO,
LOADED_DT,
REI.REINS_TYPE_DS

FROM AIGNET.dbo.RECON_TRANSACTION AS RE
INNER JOIN REINSURANCE_TYPE REI ON RE.REINS_TYPE_CD = REI.REINS_TYPE_CD
WHERE
(@RECON_TRNSCTN_ID IS NULL OR @RECON_TRNSCTN_ID=RE.RECON_TRNSCTN_ID)
AND
(@RECON_ITEM_ID IS NULL OR @RECON_ITEM_ID=RE.RECON_ITEM_ID)
AND
(@DIVISION_ID IS NULL OR @DIVISION_ID=RE.DIVISION_ID)
AND
(@REINS_TYPE_CD IS NULL OR @REINS_TYPE_CD=RE.REINS_TYPE_CD)
AND
(@TRANSACTION_NO IS NULL OR @TRANSACTION_NO=RE.TRANSACTION_NO)
AND
(@TRANSACTION_NAME IS NULL OR @TRANSACTION_NAME=RE.TRANSACTION_NAME)
AND
(@REF_NUMBER IS NULL OR @REF_NUMBER=RE.REF_NUMBER)
AND
(@POLICY_CRS_REF_NO IS NULL OR @POLICY_CRS_REF_NO=RE.POLICY_CRS_REF_NO)
AND
((RE.GL_DT IS NULL) OR (RE.GL_DT BETWEEN @GL_DTFrom AND @GL_DTTo))
AND
(@TRANSACTION_CD IS NULL OR @TRANSACTION_CD=RE.TRANSACTION_CD)
AND
(@AMOUNT IS NULL OR @AMOUNT=RE.AMOUNT)
AND
(@FILE_STATUS_CD IS NULL OR @FILE_STATUS_CD=RE.FILE_STATUS_CD)
AND
(@TRNSCTN_OWR_CD IS NULL OR @TRNSCTN_OWR_CD=RE.TRNSCTN_OWR_CD)
AND
(@ISSUE_CD IS NULL OR @ISSUE_CD=RE.ISSUE_CD)
AND
(@SUPP_ISSUE_CD IS NULL OR @SUPP_ISSUE_CD=RE.SUPP_ISSUE_CD)
AND
(@IRC_CLASSIFICATION_CD IS NULL OR @IRC_CLASSIFICATION_CD=RE.IRC_CLASSIFICATION_CD)
AND
(@UNDER_90_DAYS IS NULL OR @UNDER_90_DAYS=RE.UNDER_90_DAYS)
AND
(@OVER_90_DAYS IS NULL OR @OVER_90_DAYS=RE.OVER_90_DAYS)
AND
(@AGING_DAYS_CNT IS NULL OR @AGING_DAYS_CNT=RE.AGING_DAYS_CNT)
AND
(@VOCHER_NO IS NULL OR @VOCHER_NO=RE.VOCHER_NO)
AND
((RE.LOADED_DT IS NULL) OR (RE.LOADED_DT BETWEEN @LOADED_DTFrom AND @LOADED_DTTo))


End


-------------------------------------------------------------------------------
-- SP Usage Audit Info -- DO NOT REMOVE
-- SP Usage Section 2 - INSERT Audit Info
-------------------------------------------------------------------------------
SELECT @AuditCount = @SPUsageMode +
(SELECT ParameterFlag FROM DBPerfMon.dbo.SPUsageParameters WITH (NOLOCK)
WHERE Parameter = 'SPUsageByPass')

IF @AuditCount < 1

Begin
SELECT @SPEndTime = GETDATE()
INSERT DBPerfMon.dbo.SPUsage (
DatabaseName
, Duration
, ObjectID
, ObjectName
, UserName
)
SELECT
DB_NAME()
, DATEDIFF(ms, @SPStartTime, @SPEndTime)
, OBJECT_ID(OBJECT_NAME(@@PROCID))
, OBJECT_NAME(@@PROCID)
, dbo.fncGetLastUpdatedBy ()
End

-------------------------------------------------------------------------------
-- Absolutely NO Stored Procedure code written beyond this point
-------------------------------------------------------------------------------

View 5 Replies View Related

Stored Procedure - INSERT INTO Or UPDATE - INNER JOIN TWO TABLES

Jun 13, 2008

Hi all,can somebody help to write this stored procedure  Table1                   Table2LogID                    MigIDUserMove              LogIDUserNew               Domain                            User The two tables are inner join with LogID.If in Table2 LogID=NULL then create new dataset in Table1 (INSERT)and then Update LogID in Table2IF in Table2 LogID= 2 (or something else) then update the dataset in Table1 with the same LogID Thanks

View 1 Replies View Related

Using A Stored Procedure In A Inner Join Of A Stored Procedure

Oct 28, 2004

Hi,

it is possible to execute a stored procedures inside of a inner join of another stored procedure?
I have the problem, that I have a stored procedure which will return a resultset. This resultset should be used in a join of another stored procedure. It is possible or not?
I tried something like that, but it doesn't work for me:

CREATE PROCEDURE usp_Person
AS

SELECT
A.LastName,
A.FirstName,
B.Street
FROM
Table1 A
INNER JOIN
EXEC usp_Address B
ON
A.Id = B.Id

GO


I am thankful for a hint or a better way.

View 6 Replies View Related

Creating Stored Procedure To Use With Crystal Reports - Table Join

Dec 30, 2014

I am a newbie to SQL and I am trying to create a stored procedure to use with Crystal Reports.

I don't understand why I am having trouble joining tables to the Member table. These table joins have worked in the past.

Create Proc rpmb_FamilyPortraitDirectoryP1Test
(
@ChurchId int,
@StartFamilyName varchar(50),
@EndFamilyName varchar(50),
@MemberTypeId varchar(max) = Null,

[Code] ....

View 4 Replies View Related

How To Use If Then Statement In A Sql Stored Procedure?

Oct 17, 2007

How do you use an if then statement in a sql stored procedure? I have a table with 3 columns in it. When the tableis updated, I will only need one particular column in the database updated at a time. I will signal which column to update with an integer value(WhichColumn). So I want to key off that value to determine which column is to be updated.
Here is my pseudo code
if(WhichColumn EQUALS 1){  Update InsertEntry SET FirstColumn=Value}else if(WhichColumn EQUALS 2){  Update InsertEntry SET SecondColumn=Value}else if(WhichColumn EQUALS 3){  Update InsertEntry SET ThirdColumn=Value}
Here is a rough start to my stored procedure to incorporate the logic aboveGOCREATE PROCEDURE [dbo].[InsertEntry](  WhichColumn int  Value       nvarchar(500),  )
 
 

View 2 Replies View Related

Stored Procedure Using WHERE...IN Statement

Aug 10, 2005

Hey all, don't know where I am going wrong on this, and maybe it's just that it can't be done.  Below is the stored procedure and page code, and after that is the description of the prob*************************
SP_GetDetails
@wherestring varchar(400) ASSelect title_id, title, type, ytd_sales, pub_id from titles where title_id IN (@wherestring)*****************************This is the function that calls SP_GetDetails*****************************Public Function GetDetails(ByVal title As String) As DataViewDim DS As DataSetDim myconnection As SqlConnectionDim mycommand As SqlDataAdaptermyconnection = New SqlConnection(_ConnString)mycommand = New SqlDataAdapter("SP_GetDetails", myconnection)mycommand.SelectCommand.CommandType = CommandType.StoredProceduremycommand.SelectCommand.Parameters.Add(New SqlParameter("@wherestring", SqlDbType.VarChar, 400))mycommand.SelectCommand.Parameters("@wherestring").Value = titleDS = New DataSetTry   mycommand.Fill(DS, "Details")   Return DS.Tables("Details").DefaultViewCatch ex As Exception   Throw exFinally   myconnection.Close()End TryEnd Function****************************And this is the Function that Calls GetDetails:****************************Public Sub Get_Info(ByVal Sender As Object, ByVal e As EventArgs)Dim data As New DataLayerDim titles As String = ""Dim item As ObjectDim ltitle As StringDim i As Integer = 0For Each item In ListBox2.Items()   If ListBox2.Items(i).Selected Then      ltitle = ListBox2.Items(i).Value.ToString      If (i > 0) And Not (titles = "") Then         titles += ", '" & ltitle & "'"      Else         titles += "'" & ltitle & "'"      End If   End If   ltitle = ""   i += 1NextDataList1.DataSource = data.GetDetails(titles)DataList1.DataBind()End Sub****************************Problem:The DataSet is not getting any values.  If I run the procedure in the QA with values that I know work, in the place of @wherestring, it works fine and I get the proper results.  However if I try and give it a value (ie: Get_Info() returns "'BU1032', 'BU1111'", which are valid values, it doesn't respond)  I have a feeling that its the way that the information @wherestring gets isn't formated right to be able to plug into the SP but I don't know any other way to do it...help is greatly appreciated and if you have any questions I will answer back immediately.  thanks a ton. --Shred

View 4 Replies View Related

Help With Stored Procedure/IF Statement

Aug 7, 2007

Hey everyone!

I have a stored procedure that returns employee id's and how many shifts they have signed up for between 2 dates.

If they have less then 3 entries between the date range specified I would like update their status field to inactive.

So what I'm getting at is how would I go about doing an IF statement that would check through the results of my stored procedure to see who has worked less then 3 shifts and to execute another stored procedure to update their status.

View 10 Replies View Related

Stored Procedure If Statement

May 14, 2008

Hello,

I am new to this.

I am creating am trying to create a stored procedure that looks to see what group_id number a user selected and if they select no group_id number than it should return one of a specific set that I specify in my procedure, but if they pick just one group_id number I only want it to return those records. Here is my procedure. When I run in Managment Studio it says I have errors near my if, then and else.

Any Ideas?? Thanks in advance!

CREATE PROCEDURE EMRUserSecurityGroups @group_id int


If @group_id = "*All" then
SELECT c.user_id,c.first_name,c.last_name, b.group_id, b.group_name
FROM user_group_xref a
JOIN security_groups b on a.group_id= b.group_id
JOIN user_mstr c on a.user_id=c.user_id
WHERE b.group_id IN ('5','20','21','23','24','25','26','27','28','29','32')
else
SELECT c.user_id,c.first_name,c.last_name, b.group_id, b.group_name
FROM user_group_xref a
JOIN security_groups b on a.group_id= b.group_id
JOIN user_mstr c on a.user_id=c.user_id
WHERE b.group_id = @group_id

View 3 Replies View Related

Stored Procedure Vs SQL Statement

Jan 9, 2008

Hi Firends - What is good to use if we have to retrive records from SQL database. Should I use Stored procedures or should I write SQL statemnt in my Code.

Please let me know the advantages and disadvantages of both

Thanks in advance

View 2 Replies View Related

IF Statement In Stored Procedure

Jul 20, 2005

Here's my stored procedure:CREATE PROCEDURE proc@id varchar(50),@pswd varchar(20),@no_go int OUTPUTASSET NOCOUNT ONSELECT user_id FROM tableWHERE user_id=@id AND pswd=@pswdIF @@ROWCOUNT = 0BEGINSET @no_go = 1ENDELSEBEGINSELECT date,date_mod FROM ansWHERE user_id=@idSET @no_go = 0ENDThis statement outputs the second recordset (SELECT FROM ans) whether@@ROWCOUNT is 0 or not. Why is that and how do I stop it

View 4 Replies View Related

Stored Procedure Exec An SQL Statement

Oct 2, 2007

I have this code in a stored procedure: DECLARE @SQLString VarChar(200)
SET @SQLString = 'SELECT ' + @LookupField + ' FROM ' + @DBTable + ' WHERE (' + @IDField + ' = ''' + @IDValue + ''')'
Exec (@SQLString)
it works fine - with just one issue - I must grant select permission on the table.
Is there a way to do this WITHOUT granting the select permissions?

View 7 Replies View Related

Stored Procedure If Statement And Begin End

Jun 28, 2005

Is it necessary to include the Begin and End in this statement?  IF @CId = '102'  BEGIN   SET @102 = 1  ENDOr, can it be rewritten as...  IF @CId = '102'     SET @102 = 1  Thanks all,Zath

View 4 Replies View Related

Help Turning This Sql Statement Into A Stored Procedure

Aug 26, 2005

Hello, I need a little help turning this:SELECT RequestNum FROM Tickets WHERE ReceiptDate>='" & FromDate & "' AND ReceiptDate<='" & ToDate & "'"into a sproc because of the two different values (FromDate and ToDate) for the ReceiptDate field in the database.I have this so far (problem areas are ??):Dim AuditConnection As New SqlConnection(ConnString)Dim AuditCommand As New SqlCommand("CreateAudit", AuditConnection)AuditCommand.CommandType = CommandType.StoredProcedureAuditCommand.Parameters.Add(New SqlParameter("@??", SqlDbType.NVarChar)).Value = FromDateAuditCommand.Parameters.Add(New SqlParameter("@??", SqlDbType.NVarChar)).Value = ToDateAuditConnection.Open()Dim AuditResult As SqlDataReader = AuditCommand.ExecuteReader()AuditGrid.DataSource = AuditResultAuditGrid.DataBind()AuditConnection.Close()and:CREATE PROCEDURE CreateAudit    ??    ??ASSELECT    RequestNumFROM    TicketsWHERE    ??AND    ??GOI know I'm an idiot and this should be something simple.  Arrrgh.  Any help is appreciated immensely!!!  :)

View 2 Replies View Related

Proper Use Of IF Statement In Stored Procedure?

Dec 12, 2005

I'm trying to handle a stored procedure parameter. What needs to happen is that a particular statement shouldn't be executed if the parameter is empty. However, when I try it I get the following error:Cannot use empty object or column names. Use a single space if necessary.So, what's the correct way of doing the following?IF @filename <> ""BEGIN        UPDATE Experimental_Images SET contentType = @contentType, filename = @filename           WHERE (id = @iconNo)END

View 1 Replies View Related

UPDATE Within An IF Statement Of A Stored Procedure

Feb 10, 2006

Hi

I want to run a stored procedure which updates a password.  I
have the a table 'users' which has columns 'name' (as primary key) and
'pwd', which hold the username and password respectively.  The
stored procedure accepts @username (the username), @pwdOld and @pwdNew
(the old and new passwords).

The following procedure returns 1 if the user inputs the correct old password.

               
strCreateStoredProcedure = " " & _
                 
"CREATE PROC changePassword1 " & _
                  "( " & _
                 
"@userName  VarChar(20), " & _
                 
"@pwdOld    VarChar(50), " & _
                 
"@pwdNew    VarChar(50) " & _
                  ") " & _
                  "AS " & _
                 
"Declare @name VarChar(20) " & _
                 
"Declare @actualPassword VarChar(50) " & _
                  "SELECT " & _
                  "@name = name, " & _
                 
"@actualPassword = pwd " & _
                  "FROM users " & _
                 
"WHERE name = @username " & _
                 
"If @name is not null " & _
                 
"  If @pwdOld = @actualPassword " & _
                 
"    Return 1 " & _
                  "  Else " & _
                 
"  Return -1 " & _
                  "Else " & _
                  "Return -1"


The following procedure updates the old password to the new password.  So job done.

               
strCreateStoredProcedure = " " & _
                 
"CREATE PROC changePassword2 " & _
                  "( " & _
                 
"@userName  VarChar(20), " & _
                 
"@pwdOld    VarChar(50), " & _
                 
"@pwdNew    VarChar(50) " & _
                  ") " & _
                  "AS " & _
                 
"Declare @name VarChar(20) " & _
                 
"Declare @actualPassword VarChar(50) " & _
                 
"    UPDATE users " & _
                 
"    SET pwd = @pwdNew " & _
                 
"    WHERE name = @userName "

But using two procedures must be inefficient and slow.  However, I
have not been able to combine the two.  I would have thought I
should be able to replace "return 1" in the first procedure with the
UPDATE statement in the second procedure but I cannot save this
procedure.
i.e.

               
strCreateStoredProcedure = " " & _
                 
"CREATE PROC changePassword1 " & _

                  "( " & _
                 
"@userName  VarChar(20), " & _
                 
"@pwdOld    VarChar(50), " & _
                 
"@pwdNew    VarChar(50) " & _

                  ") " & _

                  "AS " & _
                 
"Declare @name VarChar(20) " & _
                 
"Declare @actualPassword VarChar(50) " & _

                  "SELECT " & _

                  "@name = name, " & _
                 
"@actualPassword = pwd " & _

                  "FROM users " & _
                 
"WHERE name = @username " & _
                 
"If @name is not null " & _
                 
"  If @pwdOld = @actualPassword " & _
                 
"    UPDATE users " & _
                 
"    SET pwd = @pwdNew " & _
                 
"    WHERE name = @userName "
                 
"    Return 1 " & _

                  "  Else " & _
                 
"  Return -1 " & _

                  "Else " & _

                  "Return -1"

Any ideas please?
Thanks in advance

Mike

View 4 Replies View Related

If Statement In Select Of Stored Procedure

Dec 19, 2000

Hi,

Can you use an IF statement in a Select statement within a stored procedure?

If so, how?

Mark

View 1 Replies View Related

Need To Run Stored Procedure In Select Statement

Aug 21, 2000

I am trying to execute a stored procedure in a select statement.

I have a stored procedure that returns the next number in a sequence (much like an identity column)

I am trying to do an insert statment like the following

insert into foo (field1,field2,field3) select fieldx, fieldy, exec getnextvalue from bar

Is there any way I can do this?

I do not want to use a trigger or an identity column.

View 4 Replies View Related

Stored Procedure With 2 Variables, How To Use Them In The WHERE Statement?

Sep 29, 2005

Hi!

I need a stored procedure with this basic setup:

CREATE PROCEDURE test
@Type int
AS

SELECT *
FROM
Cards
WHERE
CASE @Type = 1111 THEN CardType = 1111 ELSE CardType = 2222 END

GO


I know that the part after WHERE is wrong. But what I would like to achieve is this:

if the @type variable equals 1111 then get alla the rows with that value in the CardType-column. The same if @type = 2222, and if @type is any other value, then choose all rows regardles of the CardType value.

How can this be done?

Thanks!
/Rickard

View 2 Replies View Related

If Statement Inside A Stored Procedure!!!!

Mar 20, 2007

hi all,
i'm wondering if i can use one stored procedure in too cases, this is the senario:
i have stored procedure for admin and another one for user, they have the same select everything is the same except that in admin SP i have where @admin = admin and for user i have where @user = user
if there a way to use if and else to make this happen
this is what i did so far:

CREATE PROCEDURE [test] @admin INT, @user INT, @indexType INT as
if @indexType = 1

begin

SELECT * FROM table WHERE something IN (SELECT * FROM anothertable where admin = @admin)
end
else
begin
SELECT * FROM table WHERE user = @user
end
GO

any suggestion will be very helpful
thanks

View 2 Replies View Related

Insert With A If Statement. But Yet Not Using A Stored Procedure. Is That Possible?

Mar 12, 2004

my table:

FoodID Integer PRIMARY KEY,
FoodName varchar(255),
FoodDesc text

How do I insert into this table while checking that the FoodName do not replicate? I'm aware that with a stored procedure I'm able to you the IF EXIST statement to help me solve this problem. But if I do not wish to use the stored procedure, am I able to create a SQL string to insert while checking the condition?

Thanks in advance to the people that replied to my request! Thanks so much...

View 4 Replies View Related

Stored Procedure For Update Statement

Sep 24, 2013

I have a Table by name LAB_TEST_MASTER(MASTER TABLE)with Test_ID,Test_Name and Normal_Values columns.

Test CodeTest Name Normal Value
6 Blood Urea 20 - 45
12 HBA1C Glycoslated Haemoglobin4.0 - 6.0
86 Serum Creatinine 0.7 - 1.2
147 Fasting Blood Sugar 60 - 100  
292 POST PRANDIAL BLOOD SUGAR 5 - 150

I have one more table by name PATIENT_LAB_TESTS (TRANSACTION TABLE) with Patient_Id, Test_ID,Test_Result,and Test_Status Columns.

Patient IdTest CodeResultStatus
27924 6 51NULL
27924 12 5.5NULL
27924 86 0.9NULL
27924 147 55NULL
27924 292 59NULL

How to compare the Test_Result Column With Normal Values and Update the Test_Status Column for multiple rows in Transaction Table.

If my Test_Result value is less than Low Value of Normal_Values then i need to Update Test_Status as L and
if Test_Result value is greater than High Value of Normal_Values then i need to Update Test_Status as H and
if the Test_Result Value is in between Low and High Value of Normal_Values then i need to update a blank space

How to do this in sql server?

View 4 Replies View Related

Stored Procedure In A Select Statement

Apr 23, 2007

Hi All,

Can i run a stored procedure with in a SELECT statement
like below?

Select * from EmployeeDetails where EmployeeName in (.. Some Stored procedure to return me some set of Employee Names)

Is there a better way of doing it?
Thanks in advance

vishu
Bangalore

View 2 Replies View Related

Stored Procedure Exists Statement

Feb 26, 2008

When I created an SP in Enterprise Manager, I didn't manually type in the existence check at the inception, a la "if exists (select * from sysobjects...)". I just started with the CREATE PROC AS statement.

I noticed that if I generate a SQL script for the SP, SQL2000 automatically generates the existence check statement.

My question is, can I assume that when the SP is actually executed, SQL2000 does the exists check on its own? I EXEC'd the proc in Query Analyzer with no errors.

I just want to make sure that I don't need to enter the exists statement if it's already being done behind the scenes.

View 3 Replies View Related

Error In Stored Procedure Sql Statement

Jan 6, 2008



I am using C# to pass few paramenters to build the SQL statement in the stored procedure but when i run it i get this error:

Could not find stored procedure 'SELECT file_no, old_file_no, id_number, person_name FROM persons WHERE gender IN (null, 'MALE', 'FEMALE', 'OTHER') ORDER BY NAME'.

though the stored procedure dose exist and i don't have any problem when i staticaly write the sql statement in the stored procedure. can you pleas help..

here is my store dprocedure:



Code Block
USE shefa
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
ALTER PROCEDURE [dbo].[sp_get_all_patients]
@order_field varchar(255),
@patient_gender varchar(255)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sql_statement varchar(255)

SET @sql_statement = 'SELECT file_no, old_file_no, id_number, person_name FROM persons ' + @patient_gender + ' ORDER BY ' + @order_field
EXEC @sql_statement
-- IF @order_field = 'FILE NO'
-- SELECT file_no, old_file_no, id_number, person_name FROM persons ORDER BY file_no
-- ELSE IF @order_field = 'OLD FILE NO'
-- SELECT file_no, old_file_no, id_number, person_name FROM persons ORDER BY old_file_no
-- ELSE
-- SELECT file_no, old_file_no, id_number, person_name FROM persons ORDER BY person_name
END







and here is my C#:




Code Block
private void get_all_patients(string order_field, string patient_gender)
{
this.Cursor = Cursors.WaitCursor;
data_table = new DataTable();
// sql_connection = new SqlConnection("Data Source=.\SQLEXPRESS;initial catalog=shefa;integrated security=true");
sql_connection = new SqlConnection(public_var.sql_connection);
sql_command = new SqlCommand("sp_get_all_patients", sql_connection);
sql_command.CommandType = CommandType.StoredProcedure;
sql_command.Parameters.Add("@order_field", SqlDbType.VarChar).Value = order_field;

if (patient_gender == "ALL")
sql_command.Parameters.Add("@patient_gender", SqlDbType.VarChar).Value = "WHERE gender IN (null, 'MALE', 'FEMALE', 'OTHER'";
else if (patient_gender == "MALE ONLY")
sql_command.Parameters.Add("@patient_gender", SqlDbType.VarChar).Value = "WHERE gender ='MALE'";
else if (patient_gender == "FEMALE ONLY")
sql_command.Parameters.Add("@patient_gender", SqlDbType.VarChar).Value = "WHERE gender ='FEMALE'";
else if (patient_gender == "OTHER")
sql_command.Parameters.Add("@patient_gender", SqlDbType.VarChar).Value = "WHERE gender NOT IN ('MALE', 'FEMALE')";
else
sql_command.Parameters.Add("@patient_gender", SqlDbType.VarChar).Value = "WHERE gender IN (null, 'MALE', 'FEMALE', 'OTHER')";
data_adapter = new SqlDataAdapter(sql_command);
data_adapter.Fill(data_table);
dataPatients.DataSource = data_table;

dataPatients.Columns["file_no"].HeaderText = "File no";
dataPatients.Columns["file_no"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.TopRight;
dataPatients.Columns["file_no"].Width = 100;
dataPatients.Columns["old_file_no"].HeaderText = "OLD File no";
dataPatients.Columns["old_file_no"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.TopRight;
dataPatients.Columns["old_file_no"].Width = 100;
dataPatients.Columns["old_file_no"].Visible = false;
dataPatients.Columns["id_number"].HeaderText = "ID Number";
dataPatients.Columns["id_number"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.TopRight;
dataPatients.Columns["id_number"].Width = 100;
dataPatients.Columns["person_name"].HeaderText = "Name";
dataPatients.Columns["person_name"].DefaultCellStyle.Alignment = DataGridViewContentAlignment.TopLeft;
dataPatients.Columns["person_name"].Width = 650;
this.Cursor = Cursors.Default;
}


View 5 Replies View Related

Stored Procedure - Using Value From A Select Statement

Jul 30, 2007

Hello,

I have written a stored procedure which has in it a select statement. This returns a single record (I used the "top 1" line to ensure I only get a single record). I want to use a value returned by this statement (not the primary key) further on in the stored procedure - so assigning it to a variable would be handy.

Is this possible? If so, how do I do it?

Thanks.

View 3 Replies View Related







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