Using The AS Clause In A SQL Stored Proc -- Problem Using Datetime Column
Jul 20, 2005
I'm trying to concatenate fields in SQL stored proc for use in text
field in asp.net dropdownlist. I'm running into a problem when I try
to use a DateTime field, but can't find the answer (so far) on the
Internet. Was hoping someone here would know?
My sql stored proc:
SELECT AnomalyID, DateEntered + ', ' + Station + ', ' + Problem As
'SelectInfo'
FROM tblAnomaly
WHERE WorkOrder=@varWO AND Signoff=Null
ORDER BY DateEntered
but I get the error:
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value.
I'm not the brightest bulb on the block, so any clues greatly
appreciated!
Let's say I have a procedure called spGetData, which accepts two params ParamA and ParamB. It returns all the columns of a view that match the criteria specified by the two parameters. That works fine, no prob.
Now I want to write another stored proc that only returns certain columns from the rowset returned by spGetData. I want something like this:
CREATE PROCEDURE spGetDataList(@A int, @B int) AS SELECT colA, colB, colC FROM (EXEC spGetData @A, @B)
I've tried a few different syntaxes, and I can't seem to get this right. Please tell me there's a way to do this! If not, here's the reason I want it and maybe someone can answer this. We don't always know what search criteria a user will want, and right now we are using ASP to build our our SQL statement. We want to move to stored procs for performance and code maintainability reasons. Sometimes, we want to get the entire row back, like for detail pages. However, sometimes we only want to get back enough information to present a list to the user. We have some views with a large number of columns (~50), and we don't want to waste time returning them all when we don't need them. Finally, the logic for the stored proc to retrieve the desired rows could be long in some cases, and we don't want to maintain that logic twice. Hence, we write one proc for selecting the rows, which we call directly when we want all columns. Then we write another proc which returns a subset of the columns for performance and ease.
Can you put an ORDER BY clause in a stored procedure? What I'd like to do is have a stored procedure where the proc could be called, with an ORDER BY clause passed on as a variable,
as in: CREATE PROCEDURE dbo.select_all_from_users @order_by varchar(100) AS
SELECT * from USERS ORDER BY @order_by;
This doesn't work, I get the following nastygram thrown in my face "Error 1008: The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression itentifying a column position. Variables are only allowed when ordering by an expression refrencing a column name."
That's where I'm stuck. The variable @order_by WILL be refrencing a column name, at least it will in my opinion, but the SQL Server doesn't think so...
Hey there everyone, hopefully someone can shed some light on this problem.
Here is the stored procedure:
CREATE Procedure sp_getRequestorwCredentials
@LoginID varchar(50)
AS
SELECT Users.Name as name, Users.Email as email, Users.Manager as manager, Users.Mgremail as mgremail, Users.Empid as empid, Users.Phone as phone, Users.Dept as dept, Users.Busunit as busunit, Users.Segment as segment, Users.EmpPosition as position, Users.Region as region,
Requests.RequestDate, Users.EmpPosition, Users.Reason as reason, Users.District as district, Users.NetId, Users.Status as status
FROM Users INNER JOIN
Requests ON Users.Empid = Requests.UserId
WHERE (Users.NetId like RTRIM(@LoginID))
ORDER BY Requests.RequestDate DESC
return
GO
Now this query will work fine when using the like operator however if a user with a similar name to another user already in the table then the user may get the other persons record instead of thier own. Of course the solution here is to use the = operator instead of LIKE. This works fine when querying the DB directly however when executed within the stored proc no records are returned using the = operator even if they exact same query text works fine in a query window with a hardcoded var.
Yes I know there are SQL injection issues but the LoginID is being grabbed from the users domain login DOMAINNAME and is grabbed from the authenciation module. Since Active Directory names have a limited char set and theres not a way to pass an invalid name with text that allows for an injection attack.
two variables declared in my proc:@DATE_RANGE_START as datetime,@DATE_RANGE_END as datetime,When I execute my SP it takes 34 seconds.When I change the variables to:@DATE_RANGE_START1 as datetime,@DATE_RANGE_END1 as datetime,and add this to my sp:declare @DATE_RANGE_START datetimedeclare @DATE_RANGE_END datetimeset @DATE_RANGE_START = @DATE_RANGE_START1set @DATE_RANGE_END = @DATE_RANGE_END1the SP runs in 9 seconds (which is expected)Passing in '1/1/01' and '1/1/07' respectivly.Everything else is equal and non-important to this problem.Why does it take 34 seconds when I use the variables from the inputparameters?Interesting isn't it.Jeff
I'm attempting to pass a datetime variable to a stored proc (called via sql task). The variables are set in a previous task where they act as OUTPUT paramters from a stored proc. The variables are set correctly after that task executes. The data type for those parameters is set to DBTIMESTAMP.
When I try to exectue a similar task passing those variables as parameters, I get an error:
Error: 0xC002F210 at ax_settle, Execute SQL Task: Executing the query "exec ? = dbo.ax_settle_2 ?, ?,?,3,1" failed with the following error: "Invalid character value for cast specification". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
If I replace the 2nd and 3rd parameters with quoted strings, it is successful: exec ?= dbo.ax_settle ?, '3/29/06', '4/30/06',3,1
I'm having problems with a stored procedure, that i'm hoping someone can help me with.
I have a table with 2 columns - Username (varchar), LastAllocation (datetime)
The Username column will always have values, LastAllocation may have NULL values. Example
Username | LastAllocation ------------------------ Greg | 02 October 2005 15:30 John | 02 October 2005 18:00 Mike | <NULL>
My stored procedure needs to pull back a user name with the following criteria:
If any <NULL> dates send username of first person where date is null, sorted alphabetically, otherwise send username of person with earliest date from LastAllocation
Then update the LastAllocation column with GETDate() for that username.
This SP will be called repeatedly, so all users will eventually have a date, then will be cycled through from earliest date. I wrote an SP to do this, but it seems to be killing my server - the sp works, but I then can't view the values in the table in Enterprise Manager. SP is below - can anyone see what could be causing the problem, or have a better soln? Thanks Greg ------------------------------------------------------------------------------ ------------------------------------------------------------------------------ CREATE PROCEDURE STP_GetNextSalesPerson AS DECLARE @NextSalesPerson varchar(100)
BEGIN TRAN
IF (SELECT COUNT(*) FROM REF_SalesTeam WHERE LeadLastAllocated IS NULL) > 0 BEGIN SELECT TOP 1 @NextSalesPerson = eUserName FROM REF_SalesTeam WHERE LeadLastAllocated IS NULL ORDER BY eUserName ASC END ELSE BEGIN SELECT TOP 1 @NextSalesPerson = eUserName FROM REF_SalesTeam ORDER BY LeadLastAllocated ASC END
SELECT @NextSalesPerson UPDATE REF_SalesTeam SET LeadLastAllocated = GETDATE() WHERE eUserName = @NextSalesPerson
I'm writing a simple voting script and have columns for each options. I need to update the data based on whichever option the user picks.I.e...If the user picks option 1 then execute UPDATE mytable SET option1 = option1 + 1If the user picks option 2 then execute UPDATE mytable SET option2 = option2 + 1 Etc., etc.What's the best way to do that without building an ad-hoc SQL statement? There could be many options so I dont want to have lots of redundant SQL statements.Can I just use a varible in a stored proc and do something like this? UPDATE mytable SET @optionUserpicked=@optionUserpicked + 1Thanks in advance
Using a stored proc. I need a generic starting point. I am trying to get order categories ie. oranges, bananas, pears on the horizontal and then the daily totals vertically for each day of the month. Just say the # under ea is the total of boxes received daily. The total under each category would be determined by the receipt date. Also I need a total category for each fruit under each as month-date and year-to-date. This should be updated daily. Obviously my actual prog is more complicated then this but I just need an idea where to start. Product Oranges Bananas Pears Total Fruit 03/1/08 1 2 5 8 03/2/08 0 2 1 3 03/3/08 4 0 1 5 mtd ytd
Hi, I need to list out all the stored proc for all the DB in entire server. My inpute parameter will be table name and column1/column2/column3...etc. Please give me your input for my request. Thank, Ravi
I want to retrieve SQL 2000 Encrypted Column Data From SQL 2005 strored proc. My Stored Procedure was on SQL 2000 and it works fine....Then I restore Database From SQL 2000 to SQL 2005. The Following Statement is on my store proce.
select user_id , Encrypt(user_pass) from OpenRowset('SQLOLEDB','myserver';'sa';'mypass',databasename.dbo.users) as a
The Following Error I get When I execute the above statement.
Msg 195, Level 15, State 10, Line 1
'Encrypt' is not a recognized built-in function name.
I am working with a large application and am trying to track down a bug. I believe an error that occurs in the stored procedure isbubbling back up to the application and is causing the application not to run. Don't ask why, but we do not have some of the sourcecode that was used to build the application, so I am not able to trace into the code. So basically I want to examine the stored procedure. If I run the stored procedure through Query Analyzer, I get the following error message: Msg 2758, Level 16, State 1, Procedure GetPortalSettings, Line 74RAISERROR could not locate entry for error 60002 in sysmessages. (1 row(s) affected) (1 row(s) affected) I don't know if the error message is sufficient enough to cause the application from not running? Does anyone know? If the RAISERROR occursmdiway through the stored procedure, does the stored procedure terminate execution? Also, Is there a way to trace into a stored procedure through Query Analyzer? -------------------------------------------As a side note, below is a small portion of my stored proc where the error is being raised: SELECT @PortalPermissionValue = isnull(max(PermissionValue),0)FROM Permission, PermissionType, #GroupsWHERE Permission.ResourceId = @PortalIdAND Permission.PartyId = #Groups.PartyIdAND Permission.PermissionTypeId = PermissionType.PermissionTypeId IF @PortalPermissionValue = 0BEGIN RAISERROR (60002, 16, 1) return -3END
First of all, I've been a reader of swynk.com for quite sometime now, and I'd like to say 'thank you' to everyone who contributes.
Today, I'm the town moron.. haha I'm having issues with column level constraints. I have a varchar(50) where I want to keep *,=,#,/, .. etc, OUT OF the value input. I don't want to strip them. I simply want for sql to throw an error if the insert contains those (and other characters). The only characters that I want in the column are A-Z and 0-9. However, it's not a set number of characters per insert. It always varies... There has to be an easier way to do this than creating a constraint for every possibilty... Any help would be greatly appreciated.
I am having trouble executing a stored procedure on a remote server. On my local server, I have a linked server setup as follows: Server1.abcd.myserver.comSQLServer2005,1563
This works fine on my local server:
Select * From [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.dbo.TableName
This does not work (Attempting to execute a remote stored proc named 'Data_Add':
When I attempt to run the above, I get the following error: Could not locate entry in sysdatabases for database 'Server1.abcd.myserver.comSQLServer2005,1563'. No entry found with that name. Make sure that the name is entered correctly.
Could anyone shed some light on what I need to do to get this to work?
Hi All,Quick question, I have always heard it best practice to check for exist, ifso, drop, then create the proc. I just wanted to know why that's a bestpractice. I am trying to put that theory in place at my work, but they areasking for a good reason to do this before actually implementing. All Icould think of was that so when you're creating a proc you won't get anerror if the procedure already exists, but doesn't it also have to do withCompilation and perhaps Execution. Does anyone have a good argument fordoing stored procs this way? All feedback is appreciated.TIA,~CK
I have an ASP that has been working fine for several months, but itsuddenly broke. I wonder if windows update has installed some securitypatch that is causing it.The problem is that I am calling a stored procedure via an ASP(classic, not .NET) , but nothing happens. The procedure doesn't work,and I don't get any error messages.I've tried dropping and re-creating the user and permissions, to noavail. If it was a permissions problem, there would be an errormessage. I trace the calls in Profiler, and it has no complaints. Thedatabase is getting the stored proc call.I finally got it to work again, but this is not a viable solution forour production environment:1. response.write the SQL call to the stored procedure from the ASPand copy the text to the clipboard.2. log in to QueryAnalyzer using the same user as used by the ASP.3. paste and run the SQL call to the stored proc in query analyzer.After I have done this, it not only works in Query Analyzer, but thenthe ASP works too. It continues to work, even after I reboot themachine. This is truly bizzare and has us stumped. My hunch is thatwindows update installed something that has created this issue, but Ihave not been able to track it down.
set @AbbreviationExists = (select Short from Eaglef90.Abbreviations where Short = @SubSet) set @ImageExists = (select ImageName, Game, SubSet from Eaglef90.Images where ImageName = @ImageName and Game = @Game and SubSet = @SubSet) set @Error = 0
if @AbbreviationExists is null insert Eaglef90.Abbreviations (Short, Long) values (@SubSet, @FullSubSet)
if @ImageExists is null insert Eaglef90.Images (ImageName, Game, SubSet, Width, Height, AltText) values (@ImageName, @Game, @SubSet, @Width, @Height, @AltText) else set @Error = 1
but when I hit execute in Query Analizer I recive this error:
Msg 116, Level 16, State 1, Procedure AddImages, Line 7 Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Line 7 has this code on it:
set @ImageExists = (select ImageName, Game, SubSet from Eaglef90.Images where ImageName = @ImageName and Game = @Game and SubSet = @SubSet)
Does anyone know what is wrong with that select statement?
-- If I get used to envying others... Those things about my self I pride will slowly fade away. -Stellvia
Hi, I'm inserting a datetime values into sql server 2000 from c#
SQL server table details Table nameate_test columnname datatype No int date_t DateTime
C# coding SqlConnection connectionToDatabase = new SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=testdb;Integrated Security=SSPI"); connectionToDatabase.Open(); DataTable dt1 = new DataTable(); dt1.Columns.Add("no",typeof(System.Int16)); dt1.Columns.Add("date_t", typeof(System.DateTime)); DataRow dr = dt1.NewRow(); dr["no"] = 1; dr["date_t"] = DateTime.Now; dt1.Rows.Add(dr); for(int i=0;i<dt1.Rows.Count;i++) { string str=dt1.Rows["no"].ToString(); DateTime dt=(DateTime)dt1.Rows["date_t"]; string insertQuery = "insert into date_test values(" + str + ",'" + dt + "')"; SqlCommand cmd = new SqlCommand(insertQuery, connectionToDatabase); cmd.ExecuteNonQuery(); MessageBox.Show("saved"); } When I run the above code, data is inserted into the table The value in the date_t column is 2007-07-09 22:10:11 000.The milliseconds value is always 000 only.I need the millisecond values also in date_t column. Is there any conversion needed for millisecond values?
Example, suppose you have these 2 tables(NOTE: My example is totally different, but I'm simply trying to setupthe a simpler version, so excuse the bad design; not the point here)CarsSold {CarsSoldID int (primary key)MonthID intDealershipID intNumberCarsSold int}Dealership {DealershipID int, (primary key)SalesTax decimal}so you may have many delearships selling cars the same month, and youwanted a report to sum up totals of all dealerships per month.select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',sum(cs.NumberCarsSold) * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDMy question is, is there a way to achieve something like this:select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',TotalCarsSoldInMonth * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDNotice the only difference is the 3rd column in the select. Myparticular query is performing some crazy math and the only way I knowof how to get it to work is to copy and past the logic which isgetting out way out of hand...Thanks,Dave
I have a stored procedure "uspX" that calls another stored procedure "uspY" and I need to retrieve the return value from uspY and use it within uspX. Does anyone know the syntax for this?
I have about 5 stored procedures that, among other things, execute exactly the same SELECT statement
Instead of copying the SELECT statement 5 times, I'd like each stored proc to call a single stored proc that executes the SELECT statement and returns the resultset to the calling stored proc
The SELECT statement in question retrieves a single row from a table containing 10 columns.
Is there a way for a stored proc to call another stored proc and gain access to the resultset of the called stored proc?
I know about stored proc return values and about output parameters, but I think I am looking for something different.
I would like to know if the following is possible/permissible:
myCLRstoredproc (or some C# stored proc) { //call some T SQL stored procedure spSQL and get the result set here to work with
INSERT INTO #tmpCLR EXECUTE spSQL }
spSQL (
INSERT INTO #tmpABC EXECUTE spSQL2 )
spSQL2 ( // some other t-sql stored proc )
Can we do that? I know that doing this in SQL server would throw (nested EXECUTE not allowed). I dont want to go re-writing the spSQL in C# again, I just want to get whatever spSQL returns and then work with the result set to do row-level computations, thereby avoiding to use cursors in spSQL.
1) Rows ordered using textual id rather than numeric id
Code Snippet select cast(v.id as nvarchar(2)) id from ( select 1 id union select 2 id union select 11 id ) v order by v.id
Result set is ordered as: 1, 11, 2 I expect: 1,2,11
if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.
2) SQL server reject query below with next message
Server: Msg 169, Level 15, State 3, Line 16 A column has been specified more than once in the order by list. Columns in the order by list must be unique.
Code Snippet select cast(v.id as nvarchar(2)) id from ( select 1 id union select 2 id union select 11 id ) v cross join ( select 1 id union select 2 id union select 11 id ) u order by v.id ,u.id
Again, if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.
It reproducible on
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
and
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
In both cases database collation is SQL_Latin1_General_CP1251_CS_AS
If I check quieries above on database with SQL_Latin1_General_CP1_CI_AS collation then it works fine again.
Could someone clarify - is it bug or expected behaviour?
I am calling a stored procedure (say X) and from that stored procedure (i mean X) i want to call another stored procedure (say Y)asynchoronoulsy. Once stored procedure X is completed then i want to return execution to main program. In background, Stored procedure Y will contiue his work. Please let me know how to do that using SQL Server 2000 and ASP.NET 2.
Hi Peeps I have a SP that returns xml I have writen another stored proc in which I want to do something like this:Select FieldOne, FieldTwo, ( exec sp_that_returns_xml ( @a, @b) ), FieldThree from TableName But it seems that I cant call the proc from within a select. I have also tried declare @v xml set @v = exec sp_that_returns_xml ( @a, @b) But this again doesn't work I have tried changing the statements syntax i.e. brackets and no brackets etc..., The only way Ive got it to work is to create a temp table, insert the result from the xml proc into it and then set @v as a select from the temp table - Which to be frank is god awful way to do it. Any and all help appreciated. Kal
Hi i want to join two tables basing on like condition of the column values of two tables. My query is like this:
select pc_assign_worklist.pxRefObjectInsName AS "pxRefObjectInsName", pc_assign_worklist.pxUrgencyAssign AS "pxUrgencyAssign", pc_assign_worklist.pyLabel AS "pyLabel", pc_assign_worklist.pyAssignmentStatus AS "pyAssignmentStatus", pc_assign_worklist.pxAssignedOperatorID AS "pxAssignedOperatorID" , pc_assign_worklist.pxCreateDateTime AS "pxCreateDateTime" , pc_assign_worklist.pxCreateOpName AS "pxCreateOpName", pc_index_workparty.MemberIdentifier AS "MemberIdentifier", pc_index_workparty.LastName AS "Last Name", pc_index_workparty.FirstName AS "First Name", pc_index_workparty.pxInsName AS "pxInsName" from dbo.pc_assign_worklist, dbo.pc_index_workparty where pxAssignedOperatorID ='dasxkx1' AND pc_index_workparty.pzInsKey Like '%'+pc_assign_worklist.pxRefObjectInsName+'%' ORDER BY pxUrgencyAssign DESC
-----------
i want to compare the two columns of two tables using like or contains clause as column1 in table a has value like "hi i am" where as column2 in table2 has value "hi". I need help on how to accomplish this.
In a stored procedure I have I have dates in the format YYYYMMDD with symbols representing the first 3 digits e.g. °30903 =20030903, and I have to convert them to proper dates, and then eliminate all old data, so I replace symbols and then convert to int
