|
i have a simple stored procedure and i want to split postcode in it which could be of length either 7 or 6 and tha second part will always be of length 3. am pasting the sql here. and also the error message. Plz help me resolve this error
<code>
SELECT CONVERT(varchar, DecryptByKey(Postcode)) as Postcode,
CONVERT(varchar, DecryptByKey(Postcode)) as Postcode1,
CONVERT(varchar, DecryptByKey(Postcode)) as Postcode2,
if Postcode.length = 7
begin
set Postcode1= substring(Postcode,5,7)
set Postcode1= substring(Postcode,1,4)
end
else
begin
set Postcode1 = substring(Postcode,4,6)
set Postcode1 = substring(Postcode,1,3)
end
FROM xyz
WHERE (abc = @def)
END
</code>
<ERROR>
error:
Msg 102, Level 15, State 1, Procedure XYZ, LINE 7
Incorrect syntax near '='.
Msg 102, Level 15, State 1, Procedure XYZ, Line LINE 12)
Incorrect syntax near '='.
</ERROR>
|
|
|
|
|
What database are you using? The syntax for if Postcode.length = 7 doesn't look like it will work - certainly not in sql server. In sql server it would be
SELECT CONVERT(varchar, DecryptByKey(Postcode)) as Postcode,
CONVERT(varchar, DecryptByKey(Postcode)) as Postcode1,
CONVERT(varchar, DecryptByKey(Postcode)) as Postcode2,
POSTCODE1 = case when len(postcode) = 7 then substring(Postcode,5,7) else substring(Postcode,4,6) end,
postcode2 = case when len(postcode) = 7 then substring(Postcode,1,4) else substring(Postcode,1,3)
FROM xyz
WHERE (abc = @def)
hope this helps
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
thanx Ashfield for ur reply I figured it out earlier itself and have implemented it almost the same way.
copying my code incase it helps anyone else.
<pre>
LEN(CONVERT(varchar, DecryptByKey(Postcode))) as Postcodeln,
case when LEN(CONVERT(varchar, DecryptByKey(Postcode))) = '7' then
substring(CONVERT(varchar, DecryptByKey(Postcode)),1,4)
else
substring(CONVERT(varchar, DecryptByKey(Postcode)),1,3)
end Postcode1,
case when LEN(CONVERT(varchar, DecryptByKey(Postcode))) = '6' then
substring(CONVERT(varchar, DecryptByKey(Postcode)),4,6)
else
substring(CONVERT(varchar, DecryptByKey(Postcode)),5,7)
end Postcode2,
</pre>
Many Thanks
|
|
|
|
|
No problem, glad to have helped
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Brand New & Updated for 2009 you can sell these turnkey websites and Php scripts. Individually, or as a complete package! YOU will keep 100% of every sale! This Package gives you all that and a whole lot more. Visit us for more detail. http://www.clicknearn.net/3016-64.html
|
|
|
|
|
Stop spamming and pay for your adverts for what is probably a crap product anyway
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Hi
I have a value in Datetime Datatype as '8/8/2009 7:45:00 AM'
i want to get only
7:45 AM
How to get this...........
|
|
|
|
|
A bit messy, but
select ltrim(right(convert(varchar,getdate(),0),7))
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
The first Community Technology Preview (CTP) of Microsoft SQL Server 2008 R2 is now available to you from TechNet.
There's new features that help you manage applications and multiple servers. Report Builder 3.0 includes support for geospatial visualization for you to include mapping, routing, custom shapes.
Many other features. See SQL Server 2008 R2 CTP Open to All[^] for download and feature information.
|
|
|
|
|
brucedkyle wrote: Report Builder 3.0
But I still can't embed a SQL 2008 report in a VS 2008 winforms app b/c the report viewer does not support the new Tablix control - AAArrrgghhh. The only way to deploy a SQL 2008 report is via reporting sevices, what a PITA.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
brucedkyle wrote: There's new features that help you manage applications and multiple servers. Report Builder 3.0 includes support for geospatial visualization for you to include mapping, routing, custom shapes.
[homer simpson voice]Oohhh...shiney objects in my reports...[/homer simpson voice]
"My interest is in the future because I'm going to spend the rest of my life there." - Charles F. Kettering
|
|
|
|
|
Hi,
I m having three store procedures
1. Test_1
2. Test_2
3. Test_3
in Test_1 i get output of one date
in Test_2 i user Test_1 to get output of one week
in Test_3 i want output of 7 weeks so for that when i use Test_2 in Test_3 i got error
"Insert exec statement cannot be nested"
please help me out of this.
Thank you.
|
|
|
|
|
I looked in my crystal ball, stirred the tea leaves, and tried the medicine man lodge and I was still unable to see your query. Do you think your could be so kind as to post it here so we might be able to fathom better your problem. I can not express how happy that would make all of us.
"My interest is in the future because I'm going to spend the rest of my life there." - Charles F. Kettering
|
|
|
|
|
Jerry Hammond wrote: Do you think your could be so kind as to post it here
Apparently not - I wonder if it is embarassment or he considers his code too proprietry to post, or maybe he worked it out himself.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
No, his lawyer is preparing an NDA document for you to sign. 
Luc Pattyn [Forum Guidelines] [My Articles]
The quality and detail of your question reflects on the effectiveness of the help you are likely to get.
Show formatted code inside PRE tags, and give clear symptoms when describing a problem.
|
|
|
|
|
Luc Pattyn wrote: NDA
The National Association of Dyslexics? rofl...
(I know what it really is btw...)
|
|
|
|
|
_Damian S_ wrote: National Association of Dyslexics
No. It is a pledge that I will Never Disrespect Alcohol.
It's not necessary to be so stupid, either, but people manage it. - Christian Graus, 2009 AD
|
|
|
|
|
I have a table with these fields:
Season Day DateStart DateEnd WeekNumber MonthNumber MonthName Year
S Sunday 2/1/2009 2/1/2009 1 1 February 2009
F Saturday 1/31/2009 1/31/2009 53 12 January 2009
.
.
.
This is a daily fiscal calendar. I need to get the season, Day the season started, Date of the season start, Date of the season end, week number of season start, month name of season start, and the year of season start. Because this is daily calendar I am thinking I can get the MIN(DateStart), and WeekNumber grouped by WeekNumber and year.
Season Date_Start Week_Number Month_Number Month Year
S 2001-01-28 1 1 February 2001
S 2002-01-27 1 1 February 2002
S 2003-01-26 1 1 February 2003
S 2004-02-01 1 1 February 2004
S 2005-01-30 1 1 February 2005
S 2006-01-29 1 1 February 2006
S 2007-01-28 1 1 February 2007
S 2008-01-27 1 1 February 2008
S 2009-02-01 1 1 February 2009
Now I know the start date. I can also get the End date of each season by getting the MAX(DateEnd), and WeekNumber grouped by WeekNumber and Year.
S 2001-07-28 00:00:00 26 6 July 2001
S 2002-07-27 00:00:00 26 6 July 2002
S 2003-07-26 00:00:00 26 6 July 2003
F 2004-07-31 00:00:00 26 6 July 2004
S 2005-07-30 00:00:00 26 6 July 2005
S 2006-07-29 00:00:00 26 6 July 2006
S 2007-07-28 00:00:00 26 6 July 2007
S 2008-07-26 00:00:00 26 6 July 2008
S 2009-08-01 00:00:00 26 6 July 2009
Now I know both start dates of each season, and end dates of each season. For example, in 2001 Spring season starts on first line of first table, and ends on first line of second table. How can I put it together into one table? Basically I need to match them by row number but I don't know how to do so in SQL 2000? This is the final result set structure I need but the data is not correct:
Season Date_Start Date_End Week_Num Month_Num Month_Name Year
S 2001-01-28 00:00:00 2001-07-28 00:00:00 1 1 February 2001
S 2001-01-28 00:00:00 2002-07-27 00:00:00 1 1 February 2001
S 2001-01-28 00:00:00 2003-07-26 00:00:00 1 1 February 2001
S 2001-01-28 00:00:00 2004-07-31 00:00:00 1 1 February 2001
CodingYoshi
Artificial Intelligence is no match for Human Stupidity.
|
|
|
|
|
Hi,
Looking at your data, it seems that you have a single entry per year in both the min and max result sets. If so, you can use derived tables, something like:
Select ...
From
(
--your first (min) query
) as SeasonStart
Inner Join
(
--your second (max) query
) as SeasonEnd
on SeasonStart.Year = SeasonEnd.Year
Hope that helps.
Regards,
Syed Mehroz Alam
My Blog | My Articles
Computers are incredibly fast, accurate, and stupid; humans are incredibly slow, inaccurate and brilliant; together they are powerful beyond imagination. - Albert Einstein
|
|
|
|
|
No, that won't work because some fall seasons start in one year but end in a different year so the join won't work. Any other ideas?
CodingYoshi
Artificial Intelligence is no match for Human Stupidity.
|
|
|
|
|
I have a fairly simple stored proc in one of my databases that does a couple of simple inner joins and returns a result set. The proc accepts a user id and returns a set of gl accounts to which that user can apply invoices and such. When I pass in an admin user id, all the records come back instantly, no problem. When I use a user id with less elevated permissions, the query takes a minute and a half or more to run.
Interestingly enough, if I take the SQL in the proc, and just run it outside the proc (setting the id of course), it returns instantly with either user id.
It didn't even make a difference when I set the proc to run WITH RECOMPILE.
Can someone give me a clue of what is going on here? I'm lost.
|
|
|
|
|
What are you using to filter the user permissions, I hope you have a table that links the GL accounts to a userid/group.
When the admin is used there is no filter so instant response, if you filter the results SQL has to do a scan of the table to locate the GL accounts, put and index on the linking fields (userid, accountid etc) in the various tables. There will almost certainly be indexes on the primary tables (userid on user table, accountid on GL table) but the linking table may not have any indexes.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Actually, whether the user is admin or not, the security mechanism is the same. They have permissions to access certain branches, and those branches have access to GL accounts.
I checked the indexes and the estimated execution plans and didn't see anything strange. After some fighting with the system, it appears that all I had to do was declare a variable inside the stored procedure, set it to the value of one of the parameters and then use it in my select clause. For whatever reason, this fixed the issue (and so far, it hasn't popped up again). I'm guessing that it somehow managed to cache an execution plan that was inappropriate for the data it had.
It's still very strange.
|
|
|
|
|
Without seeing the code for the stored proc it's hard to know what the problem is.
Checking the indexes is good advice. If that doesn't work and you're still confused, you could try looking at the execution plan for the stored proc to see what it's actually doing. The SQL Server Query Analyzer lets you do this. Once you can see what the query is doing, you should have a better idea of how to fix it.
|
|
|
|
|
Maybe This[^] article can help.
Wout Louwers
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
