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
ADVERTISEMENT
Apr 27, 2005
Rather than the real code, here's a sample we came up with.
Here's the C# Code:
public class sptest : System.Web.UI.Page
{
protected System.Web.UI.WebControls.Label Label1;
private DataSet dtsData;
private void Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page here
string strSP = "sp_testOutput";
SqlParameter[] Params = new SqlParameter[2];
Params[0] = new SqlParameter("@Input", "Pudding");
Params[1] = new SqlParameter("@Error_Text", "");
Params[1].Direction = ParameterDirection.Output;
try
{
this.dtsData = SqlHelper.ExecuteDataset(ConfigurationSettings.AppSettings["SIM_DSN"], CommandType.StoredProcedure, strSP, Params);
Label1.Text = Params[0].Value.ToString() + "--Returned Val is" + Params[1].Value.ToString();
}
//catch (System.Data.SqlClient.SqlException ex)
catch (Exception ex)
{
Label1.Text = ex.ToString();
}
}
Here is the stored procedure:
CREATE PROCEDURE [user1122500].[sp_testOutput](@Input nvarchar(76),@Error_Text nvarchar(10) OUTPUT)AS
SET @Error_Text = 'Test'GO
When I run this, it prints up the input variable, but not the output variable.
View 2 Replies
View Related
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
May 22, 2015
I'm automating some of the TDE stuff and while doing so stuck somewhere. I'm unable to use section [ENCRYPTION BY PASSWORD = ''"' + @db_pswd + '"'' ] properly as in if the user (DBA) wants to pass a single quote(') in the password, then it will fail.
I used quoted_identifier OFF/ON outside my proc, no luck still.
--Create a backup of the certificate with a private key and store in a secure location
select @cmd = 'BACKUP CERTIFICATE ' + @cert_nm + ' TO FILE = ''' + @cert_file + '''' +
' WITH PRIVATE KEY ( FILE = ''' + @key_file + ''', ENCRYPTION BY PASSWORD = ''"' + @db_pswd + '"'' );'
select @cmd
--EXEC sp_executesql @cmd
View 9 Replies
View Related
Mar 18, 1999
Greets!
I have been told that simply stopping the SQL server service and backing up the data directory is all I have to do to do a backup of my data. Is this accurate?
Thanks,
Jimmy Ipock
View 2 Replies
View Related
Apr 8, 2006
In Code Behind, What is proper select statement syntax to retrieve the @BName field from a table?Using Visual Studio 2003SQL Server DB
I created the following parameter:Dim strName As String Dim parameterBName As SqlParameter = New SqlParameter("@BName", SqlDbType.VarChar, 50) parameterBName.Value = strName myCommand.Parameters.Add(parameterBName)
I tried the following but get error:Dim strSql As String = "select @BName from Borrower where BName= DOROTHY V FOWLER "
error is:Line 1: Incorrect syntax near 'V'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'V'.
Source Error:
Line 59: Line 60: Line 61: myCommand.ExecuteNonQuery() 'Execute the query
View 2 Replies
View Related
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
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
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
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
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
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
Oct 25, 2000
If I have a stored proc which has 2 input variables and for example
the 2nd one is null or not provided what is the best way to turn this null
into a wildcard. If the second one is an int and not provided should I
but a checknull statement in the proc and convert this to a wildcard %. I believe if I do I need to change the where clause "where field1 = @var "
to where field1 like @var". Any input appreciated
View 1 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
