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
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.
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
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?
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
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), )
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
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.
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
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
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
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
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?
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
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!!! :)
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.
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.
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.
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
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...
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
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.
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.
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;
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.
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
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
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 )
