|
To create a block of statements (or compound statements), BEGIN and END is used, that is:
BEGIN
statement1
statement2
END
You can modify your code something like this:
IF EXISTS (SELECT LID FROM [HomeSolutions].[dbo].[GlobalLendersList] WHERE [Name] = @Name)
RAISERROR('This value already exists.', 11, 2)
ELSE
<code>BEGIN</code>
INSERT INTO [HomeSolutions].[dbo].[GlobalLendersList]
([Name]
,[Status])
VALUES
(@Name
, '0')
INSERT INTO [HomeSolutions].[dbo].[PersonalLendersList]
([UserID]
,[LenderID])
VALUES
(@UserID , @@IDENTITY)
<code>END</code>
Regards
________________________________
Success is not something to wait for, its something to work for.
|
|
|
|
|
Thanks mate that worked a treat. I originally tried the TRANSACTION - COMMIT keywords to no avail.
It works with or without ';' at the end of each line. Is this a required part of the syntax or not?
|
|
|
|
|
Hi,
I have the following tables:
Request Table: (RequestID, Request Description, QuoteID)
Quote Table: (QuoteID, QuoteNumber)
If I have 1 Quote Record. This quote record is assigned to 3 different Requests. I want a VIEW of those tables with a JOIN which will display:
vw_QuoteRequest:
================
QuoteID
1
I DO NOT want it to display:
vw_QuoteRequest:
================
QuoteID
1
1
1
I hope you see what I mean.
Thank you.
|
|
|
|
|
What else do you want to display? I assume it's not just the QuoteID because if it was, you would just do SELECT DISTINCT QuoteID.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Actually I dont want to display anything. I have another table totalling 3:
Stage (StageID, StageNumber, ProjectID)
Request (RequestID, RequestNumber, RequestDescription, QuoteID)
Quote (QuoteID, QuoteNumber)
What I need to do is get a link between Quote and Stage. This will also provide a link to the Project Table.
I can only obtain this link if I go through the request table.
The reason I want this link is because I am querying the table according to projects and stages, which will return a set of Quotes based on that.
NOTE: This view will be used as part of another view, therefore this view will not be displaying anything.
However, Examining the senario I posted initially, you can see that my view will return 3 instances of the quote because the same quote ID is present in 3 different requests 
I hope this is clear. Please do not hesistate to ask about anything, or even suggest better methods of implementation.
Thank you for your time.
|
|
|
|
|
Thank you very much for SELECT DISTINCT!
It works now =D.
|
|
|
|
|
Hello,
I'm using sql server and I want to use a parameter in an IN clause in the command.
e.g.
select * from tableX where ID in @test
(The "expanded" version would look like: select * from tableX where ID in ('ID1','ID2','ID3') )
No matter how I declare or fill that variable I don't succeed.
Can anybody help please?
Thank you,
Wim
|
|
|
|
|
|
Thanks for your quick reply, but I don't want to use a stored procedure.
Just a simple statement with a sql variable.
declare @test varchar(600)
select @test = <list of="" id's="" in="" some="" form="">and do the select statement like this:
select * from tableX where ID in @test
(The "expanded" version would look like: select * from tableX where ID in ('ID1','ID2','ID3') )
|
|
|
|
|
@test is a variable replaced at query execution. Using your 'expanded' example, the resultant query would be looking for all rows where ID = "('ID1','ID2','ID3')". The IN would result to equal because there is only one element in the clause (@test).
SQL variables can only replace one element, not a list of more than one. You can get around this with dynamic SQL generation which uses your variable to build a new SQL string but, you indicate you don't want to use a Stored Proc.
Solution: Drop the variable and rewrite your code to build the full statement with a comma deliminated IN clause.
|
|
|
|
|
Ok, thanks for your clear reply.
|
|
|
|
|
http://www.projectdmx.com/tsql/sqlarrays.aspx
The link above gives you three examples of how you could do this.
|
|
|
|
|
Hi all,
I have a problem in a project using MySQL/C# which is: i have some users and news board such that each user has his own news (which is a huge number) and the news board is refreshed each 30 seconds for each user. so for example, i have to go to DB every 30 seconds to return about 700 records (news) for each user which makes the system very slow. these news are divided among pages (each page have 20 news) so now i want to know if there is a method to reduce number of records returned or something in paging or something to refresh current page only or ... ? i don't know. can anyone help? 
Thank you
|
|
|
|
|
|
What is the TEXT datatype? How should it be used? Is it good or bad?
I have to save a long text to the database. I don't know the size that should be saved. Is it better to use the TEXT dataType or the VARCHAR datatype.
|
|
|
|
|
quiteSmart wrote: What is the TEXT datatype?
A data type for storing text - up to 2Gb
quiteSmart wrote: How should it be used?
When you have large amounts of text to store
quiteSmart wrote: Is it good or bad?
Like anything it depends. If you pull the TEXT data out on each query whether it is required or not it is bad. If you are careful it is very good.
quiteSmart wrote: I have to save a long text to the database. I don't know the size that should be saved. Is it better to use the TEXT dataType or the VARCHAR datatype.
If the text data is going to be over 8K a TEXT data type is your only option.
|
|
|
|
|
i have read an article that says that i can use the VARCHAR to arround 2 billion characters. They say in this article that i should write the dataType like this:
VARCHAR(max)
i am trying it but it doesn't work.
the link for the article is this:here
One more question concerning the TEXT dataType,
Does it have any problems concerning the storage capacity? or Is it like the varchar? meaning that it only takes the space it needs for saving not more?
Thanks for the reply
|
|
|
|
|
Which version of SQL Server are you using?
|
|
|
|
|
andyharman wrote: Which version of SQL Server are you using?
Microsoft SQL Server Management Studio Express 9.00.2047.00
|
|
|
|
|
quiteSmart wrote: Microsoft SQL Server Management Studio Express 9.00.2047.00
That is the IDE, not the version of the database engine. If you are using this IDE and it is the only version of SQL Server you have installed then you are using SQL Server 2005
|
|
|
|
|
|
I don't have access to SQL-Server 2005 from this machine, but you should be able to create tables like:
create table MyTable(
Id int identity(1,1) not null,
MyBigColumn varchar(max) null
) I believe that Microsoft are recommending use of "varchar(max)" instead of the "text" data-type.
Regards
Andy
|
|
|
|
|
andyharman wrote: MyBigColumn varchar(max) null
I read on MSDN that i can do this, but when trying it; it doesn't work
i don't have any clue why that is happening
|
|
|
|
|
What error message do you get?
|
|
|
|
|
andyharman wrote: What error message do you get?
Syntax Error newr 'max'
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
