consider a stored procedure with a parameter @OrderID, i want to perform the following query :select * from Orders where OrderID = @OrderIDi want the condition to be true when parameter @OrderId is null so what is the syntax for that? i think there is an IF CONDITION that can be embedded with where clause.
Hi, I am trying to implement filtering on a custome paged stored Procedure, here is my curent Stored Procedure which doesn't error on complie or run but returns no records. Anyone got any ideas on how to make this work???<Stored Procedure>set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo-- =============================================-- Author: Peter Annandale-- Create date: 22/10/2006-- Description: Get Filtered Names-- =============================================ALTER PROCEDURE [dbo].[proc_NAMEFilterPaged] -- Add the parameters for the stored procedure here @startRowIndex int, @maximumRows int, @columnName varchar(20), @filterValue varchar(20)ASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;SELECT CODE, LAST_NAME, Name, TYPE, NUMBER FROM (SELECT n.CODE, n.LAST_NAME, n.FIRST_NAME + ' ' + n.MIDDLE_NAME AS Name, nt.TYPE, f.NUMBER, ROW_NUMBER() OVER(ORDER BY n.LAST_NAME) as RowNum FROM dbo.NAME n LEFT OUTER JOIN NAMETYPE nt ON n.NAME_TYPE = nt.NAME_TYPE LEFT OUTER JOIN FUNERAL f ON n.CODE = f.DECEASED WHERE @columnName LIKE @filterValue ) as NameInfo WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) -1END </Stored Procedure> Any assistance would be greatly appreciated.. Regards..Peter.
Hi Everybady Currenctly I have facing a problem on how to create a stored procedure that able to filter the data and order it dynamically on selected row. does anyone know how to do that I have successfully create a stored procedure that able to order with any colume at any direction and select it from rownumber to another rownumber. But i have failed to add another function with is where claus to do filtering on the data after ordering and selection does anybody can give me a example about that? Thanks a lot
This is for SQL Server 2000. The purpose of the procedure is to return a subset of a filtered and sorted result set. The subset, filter criteria, and sort column and sort direction can be set dynamically. It uses the rowcount technique for paging.
This would be used to drive an ASP.NET gridview which supports filtering of the data, paging, and sorting. Please let me know what improvements I can make or if you have an idea for a better solution. (I didn't put this in a vBulletin code block because personally I find two sets of scroll bars annoying, but I can if people think it's better).
CREATE PROCEDURE dbo.Books_GetFilteredSortedSubset ( -- paging @startRowIndex INT = 1, @maximumRows INT = 999999,
-- set the unique key used to ensure the rows are sorted deterministically SET @uniqueKey = 'title_id'
-- build the FROM table source used throughout this procedure SET @tableSource = 'titles t inner join publishers p on t.pub_id = p.pub_id'
-- build the WHERE search condition used to control filtering throughout this procedure
SET @searchCondition = '(1 = 1)'
IF @title IS NOT NULL SET @searchCondition = @searchCondition + ' AND (title LIKE ''%' + @title + '%'')' IF @type IS NOT NULL SET @searchCondition = @searchCondition + ' AND (type LIKE ''' + @type + '%'')' IF @price IS NOT NULL SET @searchCondition = @searchCondition + ' AND (price = ' + CAST(@price AS NVARCHAR) + ')'
-- build the ORDER BY expression used to control the sorting throughout this procedure
SET @orderByExpression = @sortColumn + ' ' + @sortDirection
-- add uniqeKey to ORDER BY statement to ensure consistent ordering of results when @sortColumn is not unique IF @sortColumn <> @uniqueKey SET @orderByExpression = @orderByExpression + ', ' + @uniqueKey + ' ' + @sortDirection
-- Get the column value at the position specified by @startRowIndex when the results are sorted in the desired sort order
SET @sql = 'SET ROWCOUNT @rowcount; SELECT @start_row = ' + @sortColumn + ', @start_row_id = ' + @uniqueKey + ' FROM ' + @tableSource + ' WHERE ' + @searchCondition + ' ORDER BY ' + @orderByExpression
-- add sql to filter the results based on criteria passed in as parameters SET @sql = 'SET ROWCOUNT @rowcount; ' + 'SELECT t.title_id, t.title, t.price, t.type, p.pub_name, p.city, p.state, p.country FROM ' + @tableSource + ' WHERE (' + @searchCondition + ') AND '
-- add sql to control the starting row IF @sortDirection = 'ASC' SET @sql = @sql + '( (' + @sortColumn + ' > @start_row) OR (' + @sortColumn + ' = @start_row AND ' + @uniqueKey + ' >= @start_row_id) )' ELSE SET @sql = @sql + '( (' + @sortColumn + ' < @start_row) OR (' + @sortColumn + ' = @start_row AND ' + @uniqueKey + ' <= @start_row_id) )'
-- add sql to control the ordering of everything SET @sql = @sql + ' ORDER BY ' + @orderByExpression
PRINT @sql
SET @parameters = '@rowcount INT, @start_row sql_variant, @start_row_id sql_variant'
Hello I am building a survey application. I have 8 questions. Textbox - Call reference Dropdownmenu - choose Support method Radio button lists - Customer satisfaction questions 1-5 Multiline textbox - other comments. I want to insert textbox, dropdown menu into a db table, then insert each question score into a score column with each question having an ID. I envisage to do this I will need an insert query for the textbox and dropdownlist and then an insert for each question based on ID and score. Please help me! Thanks Andrew
Ok, so i have this program, and at the moment, it generates an sql statement based on an array of db fields, and an array of values...
my question is this, is there any way to create a stored procedure that has multiple dynamic colums, where the amount of colums could change based on how many are in the array, and therefore passed by parameters...
if this is possible, is it then better the pass both columns and values as parameters, (some have over 50 columns)...or just create a seperate stored procedure for each scenario?? i have no worked out how many this could be, but there is 6 different arrays of colums, 3 possible methods (update, insert and select), and 2 options for each of those 24...so possibly upto 48 stored procs...
this post has just realised how deep in im getting. i might just leave it as it is, and have it done in my application...
but my original question stands, is there any way to add a dynamic colums to a stored proc, but there could be a different number of colums to update or insert into, depending on an array??
SET @UseCTD = CASE WHEN @BranchTranType IS NULL AND @BranchDivision IS NULL AND @BranchRegion IS NULL AND @BranchNbr IS NULL AND @BranchSatId IS NULL THEN 1 ELSE 0 END
SELECT DISTINCT L.ProviderId, L.OriginalProviderId, S.SourceCode, SG.SourceGroupCode, SGDescription=SG.Description, RG.ReportGroupId , ReportGroupDesc=RG.Description , L.VendorId, VEN.VendorName, B2.BranchTranType, B2.BranchDivision, B2.BranchRegion, B2.BranchName, L.BranchNbr, L.BranchSatId , Branch=CONVERT(varchar, L.BranchNbr) + L.BranchSatId, LoNbr = ISNULL(L.LONbr, 9999), LoName = ISNULL(LO.LOName, ' - NOT ASSIGNED -') , LoEmploymentStatus = LO.EmploymentStatus, LeadCount = 1, Completed = CASE WHEN L.RecCompleted = 'C' THEN 1 ELSE 0 END, InCompleted = CASE WHEN L.RecCompleted = 'I' THEN 1 ELSE 0 END , NoContact = CASE WHEN LD.Disposition = 16 THEN 1 ELSE 0 END, Contacted = CASE WHEN LD.Disposition <> 16 THEN 1 ELSE 0 END, Pending = CASE WHEN LD.Disposition IS NULL THEN 1 ELSE 0 END, NoSale = CASE WHEN LD.Disposition <> 1 THEN 1 ELSE 0 END, Apps1003 = CASE WHEN LD.Disposition = 1 THEN 1 ELSE 0 END , AppraisalsOrdered = CASE WHEN ISNULL(T3._@8034, 0) = 0 THEN 0 ELSE 1 END, ClosedApps= CASE WHEN (@UseCTD = 1 AND SMT2._H0770 IS NOT NULL) OR SMT._H0770 IS NOT NULL THEN 1 ELSE 0 END, LoanVolume = CASE WHEN @UseCTD = 1 AND LD.CTDLoanNbr IS NOT NULL THEN ISNULL(T1._@2026, 0) ELSE ISNULL(T1._@2026, 0) END, OrigLoanBalance = CASE WHEN @UseCTD = 1 AND LD.CTDLoanNbr IS NOT NULL THEN ISNULL(SMT2._H0360, 0) ELSE ISNULL(SMT._H0360, 0) END, Revenue = CASE WHEN @UseCTD = 1 AND LD.CTDLoanNbr IS NOT NULL THEN ISNULL(SMT2.calc_TotalIncomeDollars, 0) ELSE ISNULL(SMT.calc_TotalIncomeDollars, 0) END, MarketingCost = CASE WHEN L.VendorId IN (59, 60) THEN 0 ELSE ISNULL(S.Cost, 0) END, CAM.LeadTypeId, SMT.calc_TotalIncomeDollars, SP.SubjectAddrState, SP.SubjectSCF, S.LeadFormLabel FROM MLS..tbl_MLS_Leads L (NOLOCK) INNER JOIN MLS..tbl_MLS_LeadDispositions LD (NOLOCK) ON (LD.ProviderId = L.ProviderId) INNER JOIN MLS..tbl_MLS_Branches B1 (NOLOCK) ON (B1.BranchNbr = L.BranchNbr AND B1.BranchSatId = L.BranchSatId) INNER JOIN MLS..vw_MLS_AUCodeSecurity AU (NOLOCK) ON (B1.AUCode = AU.AUCode AND AU.UserId = @UserId) INNER JOIN CHEC..SMT_Branches B2 (NOLOCK) ON (B1.BranchNbr = B2.BranchNbr) INNER JOIN MLS..tbl_MLS_SubjectProperties SP (NOLOCK) ON (SP.ProviderId = L.ProviderId) LEFT JOIN CHEC..TMRPT100 T1 (NOLOCK) ON (T1._@LOAN# = LD.TMOLoanNbr) LEFT JOIN CHEC..TMRPT300 T3 ON (T1._@LOAN# = T3.R3LOAN) LEFT JOIN CHEC..SMT3 SMT (NOLOCK) ON (SMT._H0010 = LD.TMOLoanNbr) LEFT JOIN CHEC..SMT3 SMT2 (NOLOCK) ON (SMT2._H0010 = LD.CTDLoanNbr) LEFT JOIN CHEC..SRVDSR SRV (NOLOCK) ON (SMT._H0010 = SRV._LOAN_NUM) LEFT JOIN CHEC..SRVDSR SRV2 (NOLOCK) ON (SMT._H0010 = SRV2._LOAN_NUM) LEFT JOIN MLS..tbl_MLS_MarketingSources S (NOLOCK) ON (S.SourceId = L.SourceId) LEFT JOIN tbl_MLS_MarketingSourceGroups SG (NOLOCK) ON (S.SourceGroupId = SG.SourceGroupId) LEFT JOIN tbl_MLS_ReportGroups RG (NOLOCK) ON (RG.ReportGroupID = SG.ReportGroupID) LEFT JOIN ( SELECT LONbr, LOName = FullName, EmploymentStatus, ReHireDate, HireDate, TermDate
FROM MLS..vw_MLS_UsersWithLONbrs (NOLOCK) ) LO ON (LO.LONbr = L.LONbr) LEFT JOIN MLS..tbl_MLS_Campaigns CAM (NOLOCK) ON (CAM.CampaignId = SG.CampaignId) LEFT JOIN MLS..tbl_MLS_Vendors VEN (NOLOCK) ON (VEN.VendorId = L.VendorId)
WHERE L.DateReceived BETWEEN @BeginDate AND @EndDate + ' 23:59:59' AND (CASE WHEN @ReportGroupId IS NULL THEN 1 WHEN @ReportGroupId = RG.ReportGroupId THEN 1 ELSE 0 END) = 1 AND (CASE WHEN @BranchTranType IS NULL THEN 1 WHEN @BranchTranType = B2.BranchTranType THEN 1 ELSE 0 END) = 1 AND (CASE WHEN @BranchDivision IS NULL THEN 1 WHEN @BranchDivision = B2.BranchDivision THEN 1 ELSE 0 END) = 1 AND (CASE WHEN @BranchRegion IS NULL THEN 1 WHEN @BranchRegion = B2.BranchRegion THEN 1 ELSE 0 END) = 1 AND (CASE WHEN @BranchNbr IS NULL THEN 1 WHEN @BranchNbr = L.BranchNbr THEN 1 ELSE 0 END) = 1 AND (CASE WHEN @BranchSatId IS NULL THEN 1 WHEN @BranchSatId = L.BranchSatId THEN 1 ELSE 0 END) = 1 AND L.ProviderId = L.OriginalProviderId AND L.VendorId NOT IN (59, 60, 131) -- Exclude Turndown or Ghost leads AND L.Deleted = 0
I need to add the field DateReceived from the following view:
dbo.vw_MLS_Leads
How should I code this?
As always thanks for the great answers and suggestions in advance.
I want to set a filter in my statement. Normally it is no problem, but this time I want to set a filter only on a part of the field.
For example: In my table with ordernumbers, I have orders with are starting with 'FO', VO' (FO0001, FOO0002, VO0001, VO0002, ect etc) I want to set a filter in my statement that I only wish to see the ordernumbers starting with 'FO'.
I got a problem which looks like the same with a futher topic of me (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1156355&SiteID=1), but this one is a little bit more difficult.
I want to set a filter on a field, but the it must set a filter on a part of the field. I think it will be more clear with an example:
I have a list with ItemNumbers and ItemDescriptions. ItemNo ItemDescription 1 Pineapple yellow 2 Cauliflower size 6 3 Orange sweet yellow etc. etc.
Now I want that the user of this report can set a filter just by entering a part of the description. So, if he enters 'flower' he only see the cauliflower. If he enters 'yellow' he only see the pineapple and the orange, etc. etc. If a blank value is left, all the item must be shown.
Can some one help me out by solving this problem??
new question about an old topic (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1232451&SiteID=1):
I want to set a filter on a field, but the it must set a filter on a part of the field. I think it will be more clear with an example:
I have a list with ItemNumbers and ItemDescriptions. ItemNo ItemDescription 1 Pineapple yellow 2 Cauliflower size 6 3 Orange sweet yellow etc. etc.
Now I want that the user of this report can set a filter just by entering a part of the description. So, if he enters 'flower' he only see the cauliflower. If he enters 'yellow' he only see the pineapple and the orange, etc. etc. If a blank value is left, all the items must be shown.
After this, Jarret, suggest to me to insert the next code into my query:
where ItemDescription like '%' + @ItemDescParam + '%'
This helped, but I want to have 2 options extra: 1) The part of the description which the user inserts must be independent of capitals 2) It also have to be possible that the user inserts more than one part. For example "pine" and "fellow"
Hello,I have a stored procedure: -- Get an individual league match by IDALTER PROCEDURE [dbo].[mb_League_GetLeagueMatchByID]( @LeagueMatchID int)ASSET NOCOUNT ONSELECT * FROM mb_LeagueMatch WHERE mb_LeagueMatch.LeagueMatchID = @LeagueMatchIDThe mb_LeagueMatch table has a column named IsActive that is a bit datatype.The value for all rows is set to true (in database explorer in visual studio 2005).When I execute the above stored procedure I always get -1 (I'm guessing that means null) as a result for IsActive if it was true and 0 when false (as expected).However, when I run a query on the database for the same parameter, I get the expected 1 as the value for IsActive.Has anyone seen this before?Thanks,Howard
I need to update a field with the code below, is it possible to do this with a table join? The Query Analyzer is giving me an error at "Join".
An example: (ManufSerNo is a field in table ASSETS, Status is a field in table HISTORY)
GO
CREATE PROCEDURE sp_ChngeAssetStatus
@ManufSerNochar(10), @Statusvarchar(1)
AS
UPDATE HISTORY SET Status = @Status JOIN ASSETS ON HISTORY.AssetID = ASSETS.AssetID WHERE ManufSerNo = @ManufSerNo --ELSE --RAISERROR ('Sorry, but the Asset ID specified does not exist. Record not updated.', 10 , 1)
I am writing a utility that creates Java code to access a database. I am looking for a way to get a list of fields and types that are returned by an sproc. Is there any easy way to get this from the master? Do you need to parse the SQL? This list would be like what Visual Studio.NET shows, or interdev if I remember correctly.
I am very new to stored procedures and fairly new to SQL in general. I have a stored procedure that looks like this:
CREATE PROCEDURE sp_UpdateProductsTable @prodName varchar(50), @prodManufacturer varchar(50), @prodShortDescrip text, @prodLongDescrip text, @prodCatId int, @prodPrice varchar(6), @prodSortOrder int, @prodActive int, @prodId int AS if( @prodId <> 0 ) begin update Products set Name = @prodName, Manufacturer = @prodManufacturer, ShortDescription = @prodShortDescrip, LongDescription = @prodLongDescrip, CategoryID = @prodCatId, Price = @prodPrice, SortOrder = @prodSortOrder, Active = @prodActive where ID = @prodId;
select ID as prodId from Products where ID = @prodId
end if( @prodId = 0 ) begin insert into Products ( Name, Manufacturer, ShortDescription, LongDescription, Price, CategoryID, SortOrder, Active ) values( @prodName, @prodManufacturer, @prodShortDescrip, @prodLongDescrip, @prodPrice, @prodCatId, @prodSortOrder, @prodActive );
SELECT SCOPE_IDENTITY() AS prodId end
GO
I have recently added two new fields to the database that I need the stored procedure to act upon, but adding them into the above code does not work. I read somewhere that this would happen with new fields, but I do not know how to fix this issue. Could someone point me in the right direction and help me out? I also have some other tables and SP's that I need to update, but its the same issue, I think so fixing this one should help me out across the board.
I have this stored procedure that loops through a table and updates acouple of fields. For some reason one of the fields is not beingupdated. If I run the same code from query analyzer, it works fine.Let me know if anyone can figure out why @lastscandate would ever beNULL. If it is null it should be equal to @maildate. The senerio thatseems to fail is when no records are returned from the select statementto fill in @lastscandate. This should then active the next ifstatement and set the @lastscandate equal to the @maildate. MailDateis always filled in in the database and LastScanDate will be NULL.Thanks for your help.DECLARE c1 CURSOR LOCAL FORSELECT m.id, m.acctno, m.ordid, m.cycle FROM master m WITH (nolock)WHERE m.printstatus IN ('ST', 'ML') AND (m.batchid IS NULL OR m.batchid= 0) AND (m.maildate ='' OR m.maildate IS NULL)AND NOT EXISTS(SELECT * FROM packagemaster p WITH (nolock)WHERE m.acctno = p.acctno AND m.ordid = p.ordid AND m.cycle = p.cycleAND p.status NOT IN ('BM', 'PM'))OPEN c1FETCH FROM c1 INTO @mid, @acctno, @ordid, @cycleWHILE @@fetch_status = 0BEGIN--Get MailDate from Manifest - if NULL then use GetDateset @maildate = NULLSELECT @maildate = MAX(whenmailed) FROM manifest WITH (nolock)WHERE acctno = @acctno AND ordid = @ordid AND cycle = @cycleif @maildate is NULLset @maildate = getdate()--Get Last Scan Date from Transactions - if NULL then use MailDateset @lastscandate = NULLselect @lastscandate=max(actiondate) from transactions whereacctno=@acctno and ordid=@ordid and cycle=@cycle and actionid=303if @lastscandate is NULLset @lastscandate = @maildateBEGIN TRANSACTIONUPDATE master SET printstatus = 'ML', maildate = @maildate,lastscandate=@lastscandateWHERE id = @midINSERT INTO transactions (initials, actionid, machinelogin, acctno,ordid, cycle, program) VALUES ('RLT', 55, 'Mars', @acctno, @ordid,@cycle, 'Update Mail Dates')COMMIT TRANSACTIONFETCH NEXT FROM c1 INTO @mid, @acctno, @ordid, @cycleENDCLOSE c1
Our phone system keeps a list of events, such as login and logout, which I'm doing a self join on to get the start and end times so I can calculate the time between the start and end of each event during a time period.
Then I stuffed the self joins into a couple views for the events I'm interested in...
The problem is that this makes the "end time" a calculated field, so when I use it as part of the filter it takes forever. I need to account for events ending within the time period, and events starting within the time period... so I need to filter on both the start and end time in the initial select. Filtering the start time first in a subselect, then the end time in the main select is quick but can lose things that started before the start time.
So how can I speed up the filtering on that calculated column?
Or am I going to have to get really creative with unions?
I import a flat file from a legacy system, and then convert it into a single table. That works simply enough.
Then I have a SP that querys that table using a parameter for an accountID. My business tier calls that SP and returns the results to the calling tier (my web application). Easy enough...
Now for the question. The people who created the flat file (written in COBOL) decided to use "codes" to represent data. So, for instance, if I'm looking for the account plan, I can expect to see characters like ], or [, or +, etc... These characters have a special meaning, like:
] = Plan A [ = Plan B + = Plan C, and so on.
Currently, the web application displays those characters, but I want it to display the actual plan name. Is there a way that when I execute the SP, the SP could pull the necessary records, and whenever it encounters a certain "plan" character, it could convert it into a "readable" name? Say that it sees that the plan_type field has a value of "]" for twenty records, so it converts those twenty records' plan_type value from "]" into "Plan A"? I'm not sure if I can do that, but I want to at least evaluate the option if I can.
I've evaluated other options, like using a CASE statement in my code, but I shot that down quickly...for obvious reasons. I don't wanna be changing my web application or business tier each time these guys update a plan name, or add a new one, delete an existing one, etc...
I've also thought about creating a dictionary table than contains the plan's code and its name, and then just INNER JOIN the first table with the dict table. This would keep my SP very simple (it's very straight-forward right now, and I like that). That way, if a plan name is ever changed, or a new one is added, I simply update the dict table using a simple query. However, if my SP is doing the conversion, I could just as easily update the SP.
Either of these methods would work for me, and I *do* know how to do the latter (dict table). However, there are quite a few other fields that I may have to do this for. I believe when I left for the day on Friday, my last count was 14 fields total that needed translation. That would mean 14 different dict tables! That could certainly affect my SP performance with all those INNER JOINS!
Therefore, I'm certainly interested in figuring out if it's possible to do the former method (SP), and then I shall decide which method is best for my situation.
Feel free to include your thoughts on which process you think is better as well. I'm really riding the fence with this one. However, if I can't find out how to change field values in my SP, then obviously I'll make a decision very quickly...
I want to sort a stored procedure based on a variable passed to it... what is the easiest way to do this? Here's an example of what I want to do:
sp_select_thing 10, 'thing_name', 'asc'
It would run the query then somehow evaluate parameters 2 and 3 to be placed in the ORDER BY clause. I'm not sure if they should be quoted as strings or not, I don't have an idea how to pass a "reference to a variable" as a parameter to a stored procedure... or even if such a thing is possible
I need to write a sp to fill a date field, if another field in another table is true. need the date to reflect todays date(the date the field was marked true). I know this is an easy one but I am over thinking it. please help.
i have a problem with copying (read it from one row and set it to another) a ntext field in a stored procedure. I know how to get a pointer to the data and how to read from it.
But i want to copy the whole data.
Does anybody know how to do that?
Thanks in advance
A.Gempp
------------------------------- I'm using SQL Server 2000
I have a field that is stored as a smalldatetime but I want to filter on that field only for the date. How do I ignore the time stamp and only go by the date?
Hi,I have a date/time field in a SQL2000 database, and what I would liketo do is to filter on a specific part of the field, for example thetime or hour.Supposing I have a set of data for the last 5 years and would like tofilter out any records which are outside working hours i.e. I wouldlike to show records where the time is between 9am and 5pm.Does anyone know if there is a simple way to do this in SQL? If yousimply don't specify the date part in the where clause (e.g. WHEREissuedatetime between '09:00:00' and '17:00:00') it defaults it to1900-01-01 so basically no data is returned.The only way I can see to do this is by using the DATEPART function,converting it to a varchar, appending 1900-01-01 on to it andconverting it to a datetime, and then using the where clause as statedabove. This is quite a long-winded way, however. Any other suggestions?Thanks,Matt
I am currently writing a report that will use as one of the filters the date (in this case it is the date of service of a physician office visit).
however the DB developers included the date and the time of the visit in one field so my resulting data set contains 4/13/2007 4:30pm for example, so using an @date parameter as a filter on the data field doesn't work because when you run the report and enter the date in the parameter it doesn't return anything.
I am an SQL Report writer amateur so I am sure there is an easy fix, any and all help will be appreciated.
I am creating an app that allows the user to change the order of the list by changing a value in a displayOrder field. I'd love a button for move up /move down move bottom/move top and then pass that parameter to a stored procedure and it would renumber all the items in the list. Example ItemID description DisplayOrder Action0 item 1 0 Moveup/move down1 item 2 1 Moveup/move down2 item 3 2 Moveup/move down So clicking on move up on item 2 would pass and itemID, Action and perhaps a list id to a stored proc and it would renumber the list. I'm assuming it would be done with a loop but I've never tried that.. suggestions? Thanks - Mark
If I have a column named "Login" in a SQL Table (I am sharing with another application) that I am using a stored procedure to acquire the information from, how can I trranspose its name to match code already written in a Web App to get the data. There is a web app already created that has the followig code to get the data from the database Dim strSQL ast string = "UsersSelectCommand" intLoginID = objDataReader("LoginID")
My stored procedure is the following: CREATE PROCEDURE UsersSelectCommand/* ( @parameter1 datatype = default value, @parameter2 datatype OUTPUT )*/AS Select Lastname, FirstName, Login from Users Order by LastName GO The stored procedure will return "Login" instead of "LoginID" that I am wanting. How can I modify the Stored Procedure to change the LoginID to Login.
i have a student table and i created a stored procedure to insert a new student in this table but student_id field wich i put it as primary key got error because allready record has same value .how i can know the last row's student_id value and input a new valid value in one stored procedure thanks
best solution for this stored procedure query.I'm getting the following error:
Column 'dbo.Applicants.submitted' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause. Here is my select query:
Code: SELECT DISTINCT DATENAME(MONTH, submitted) AS mon, MAX(submitted) AS SubmitDate FROM dbo.Applicants WHERE ((CASE WHEN MONTH(submitted) > 8 THEN YEAR(submitted) + 1 ELSE YEAR(submitted) END) = @AcYr) ORDER BY SubmitDate
The submitted field is a date field.I don't want to add the submitted field to Group By as I want to group by month not date.Is there any solution to avoid grouping by date?
Hallo !I have a Table with a column "ordernumber"ordernumberA12A45A77A88Is it possible to create a stored procedure which makes a string of these column ?Result: string = ('A12','A45','A77','A88')Thanks !aaapaul
