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


ADVERTISEMENT

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

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 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 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

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

Trying To Use Stored Procedure In A Select Statement

Jul 7, 2006

Hi there, I am trying to return data from a select statement that is running a stored procedure.

I am running on SQL 2000 SP4. I have created a 'loopback' linked server as described in a technet article. (It's pointing at itself) I can then use the following select statement to get data back in the select statement from a stored procedure.

select * from openquery(loopback,'exec DWStaging.dbo.PokerStaging')

I am trying to get data back from my own Stored Procedure, but continue to get the following error:

Server: Msg 7357, Level 16, State 1, Line 1
Could not process object 'exec DWStaging.dbo.PokerStaging'. The OLE DB provider 'SQLOLEDB' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to process the object:ProviderName='SQLOLEDB', Query=exec DWStaging.dbo.PokerStaging'].

If I try the same syntax with the sp_who stored procedure, it works and I get data back fine. But, If I try it with the sp_who2 stored procedure I get the same error as above.

select * from openquery(loopback,'exec sp_who') --> Works fine
select * from openquery(loopback,'exec sp_who2') --> Doesn't work

Does anyone know what the difference is between the Stored Procedures that work with this syntax and those that don't? Is there a way I can change my stored procedure such that it would cause it to work?

PS: The following code was used to create the linked server, and then a security pass though as a certain account was addedDECLARE @provstr varchar (2000)
SET @provstr = 'PROVIDER=SQLOLEDB;SERVER=' + @@SERVERNAME
EXEC sp_addlinkedserver 'loopback', @srvproduct = 'MSSQL',
@provider = 'SQLOLEDB', @provstr = @provstr



View 4 Replies View Related

Stored Procedure In SELECT Statement ?

Nov 8, 2006

Hello,

I want to recover rows returned by a stocked procedure into a select statement, something like this:

select * from (exec sp_helpuser) us where UserName like 'dbo'

This syntax is false. Is there a way to do this?

Thank you

View 6 Replies View Related

How To Put A Select Statement And Get Results In A Stored Procedure

Feb 2, 2008

Hello,I have written a stored procedure where I prompt the user for the Year and the Month (see below)How do I take the variable @TheMonth and find out how many days is in the month and then loop to display a total for every day in the selected month.Can someone please point me in the right direction.
CREATE PROCEDURE crm_contact_frequency_report
@TheYear         varchar(4),@TheMonth      varchar(2)
AS
SELECT   
/* EMAILS (B) */(SELECT COUNT(*) FROM dbo.CampaignResponse A   INNER  JOIN Email B ON A.subject = B.subject  WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth)  AND   (B.directioncode = 1)) AS    Total_EmailOutgoing,
(SELECT COUNT(*) FROM dbo.CampaignResponse A   INNER  JOIN Email B ON A.subject = B.subject  WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth)  AND   (B.directioncode = 0)) AS    Total_EmailImconing,
(SELECT COUNT(*) FROM dbo.CampaignResponse A   INNER  JOIN Email B ON A.subject = B.subject  WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth)  AND   (B.directioncode IS NULL)) AS    Total_EmailNotListed,
(SELECT COUNT(*) FROM dbo.CampaignResponse A   INNER  JOIN Email B ON A.subject = B.subject  WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth)) AS    Total_All_Emails,
/* PHONE CALLS (C) */(SELECT COUNT(*) FROM dbo.CampaignResponse A   INNER  JOIN PhoneCall C ON A.subject = C.subject  WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth)  AND   (C.directioncode = 1)) AS    Total_CallOutgoing,
(SELECT COUNT(*) FROM dbo.CampaignResponse A   INNER  JOIN PhoneCall C ON A.subject = C.subject  WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth)  AND   (C.directioncode = 0)) AS    Total_CallIncoming,
(SELECT COUNT(*) FROM dbo.CampaignResponse A   INNER  JOIN PhoneCall C ON A.subject = C.subject  WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth)  AND   (C.directioncode IS NULL)) AS    Total_CallNotListed,
(SELECT COUNT(*) FROM dbo.CampaignResponse A   INNER  JOIN PhoneCall C ON A.subject = C.subject  WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth)) AS    Total_All_Calls,
/* FAXES (D) */(SELECT COUNT(*) FROM dbo.CampaignResponse A   INNER  JOIN Fax D ON A.subject = D.subject  WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth)  AND   (D.directioncode = 1)) AS    Total_FaxOutgoing,
(SELECT COUNT(*) FROM dbo.CampaignResponse A   INNER  JOIN Fax D ON A.subject = D.subject  WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth)  AND   (D.directioncode = 0)) AS    Total_FaxIncoming,
(SELECT COUNT(*) FROM dbo.CampaignResponse A   INNER  JOIN Fax D ON A.subject = D.subject  WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth)  AND   (D.directioncode IS NULL)) AS    Total_FaxNotListed,
(SELECT COUNT(*) FROM dbo.CampaignResponse A   INNER  JOIN Fax D ON A.subject = D.subject  WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth)) AS    Total_All_Faxes
FROM   CampaignResponse AGO

View 2 Replies View Related

Building A Dynamic Sql Statement Into Stored Procedure

Apr 19, 2008

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
)

View 10 Replies View Related

Call A Stored Procedure From A SELECT Statement

Nov 28, 2005

Is there a way to call a stored procedure within a SELECT statement?Example;-----------SELECT FirstName,           LastName,           (EXEC UniqueID_KEYGEN @keyval output) AS UniqueIDINTO #tNewEmployeeFROM EmployeeTable-----------SELECT *FROM #tNewEmployeeThe return from the temp table would have a unique ID ready to insert into another table.  Our DBA has this stored procedure to create unique ID's and is to be used on all INSERTS.  I was used to having a Identity field do this for me, I don't know why we have to do it his way.  Except for the reason of sequence and easily get the next record.  But we don't use URL variables, only FORM or SESSION.Thanks for your help in advance.

View 2 Replies View Related

Stored Procedure In SELECT Statement?? Urgent

Sep 26, 2001

I have a stored procedure in an Informix-database that returns a string. Its used in a SELECT statement like this.
SELECT t1.id, t2.id, sp_name(t1.id, t2.id) FROM table1 t1, table2 t2

I want to write it in SQLserver. I have tried this syntax but get error.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 't'.

SELECT t1.id, t2.id, dbo.sp_name(t1.id, t2.id, "") FROM table1 t1, table2 t2

Can I use stored proc in this way in SQL-server?

View 2 Replies View Related

Can A Stored Procedure Be Executed From Within A Select Statement?

Dec 2, 2005

Can a stored procedure be executed from within a select statement?

Given a store procedure named: sp_proc

I wish to do something like this:

For each row in the table
execute sp_proc 'parameter1', parameter2'...
end for
...but within a select statement. I know you can do this with stored functions, just not sure what the syntax is for a stored procedure.

View 2 Replies View Related

Call Stored Procedure Within SELECT Statement

Apr 17, 2004

Can this be done? I want to call a stored procedure from inside a select statement. Since you can nest select statements, I thought it might be possible but I have no idea how to do it.


USE NORTHWIND
GO

CREATE TABLE tbA (
Item int NOT NULL,
Value int NOT NULL
) ON [PRIMARY]

GO

INSERT INTO tbA (Item, Value)
SELECT 1, 10 UNION ALL
SELECT 2, 5 UNION ALL
SELECT 3, 2
GO

CREATE PROCEDURE usp_SquareIt

@iItem int

AS

declare @iValue int
SELECT @iValue = Value FROM tbA
SELECT @iValue * @iValue AS Result

GO

SELECT Item,
EXECUTE usp_SquareIt Item AS Squared ---- can this be done
FROM tbA
GO

DROP TABLE tbA
GO

DROP PROCEDURE usp_SquareIt
GO


Any thoughts?

Mike B

View 1 Replies View Related

Call A Stored Procedure In The Select Statement

Feb 21, 2007

Is it possible to call a stored procedure in a select statement?

For example:
SELECT Buyer, Country, (exec the sp_test*) as ItemList.....

*sp_test is a stored procedure with buyer and country as the input parameters and output a concatenated item numbers (example: F12345,A1023,C40165).

View 20 Replies View Related

Results Of SQL SELECT Statement Into Stored Procedure

Jul 23, 2005

I need to run a stored procedure on all the results of a select query.For a simplified example, I'd like to be able to do something likethis:SELECT JobID, QtyToAddFROM JobsWHERE QtyToAdd > 0Then for the results of the above:EXEC sp_stockadd @JobID, @QtyToAddWhere the above line ran for every result from the above select query.Anyone able to shed some light on how to do this?Many thanks,James

View 2 Replies View Related







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