Database

Blue_Boy31-Dec-09 22:16

Blue_Boy

31-Dec-09 22:16

Here it is!

create procedure InsertUser
@username varchar(200)
as
Begin
if(select count(*) from users where username=@username) = 0)
begin

insert into users values(column1,column2......)

end
end

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

www.aktualiteti.com

yadlaprasad31-Dec-09 22:20

yadlaprasad

31-Dec-09 22:20

Hi ,

Thanks for reply

in this stored proc i want to return some value. so that i can handle in my C# sharp code.
i this case i want else condition and some return value

fttyhtrhyfytrytrysetyetytesystryrty

Blue_Boy31-Dec-09 22:33

Blue_Boy

31-Dec-09 22:33

Then I will suggest you to use function

create function InsertUser
@username varchar(200)
as
Begin

return 0 --By default user is not inserted and return value have value 0

if(select count(*) from users where username=@username) = 0)
begin

insert into users values(column1,column2......)
return 1 --User is inserted and return value have value 1

end

end

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

www.aktualiteti.com

Looking for field name standards documents

Niladri_Biswas3-Jan-10 15:27

Niladri_Biswas

3-Jan-10 15:27

Try this

CREATE PROCEDURE [dbo].[USP_CheckUserAvailability]
-- Add the parameters for the stored procedure here
(
@UserName AS VARCHAR(50)

)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
IF EXISTS( SELECT [User Name] FROM tbl_User WHERE [User Name] = @UserName )

BEGIN
SELECT * FROM tbl_User

END

ELSE

INSERT INTO tbl_User ([User Name]) VALUES (@UserName)
END

Hope this helps
Smile | :)

Niladri Biswas

Ray Cassick31-Dec-09 21:54

Ray Cassick

31-Dec-09 21:54

Not answers really, just pointers that people may know of to any industry and business segment specific field name standards documents out there.

Things that address naming conventions across various industry\business segments so that I can look to normalize a set of tables for a generalized system that could cross industry boundaries and still manage to keep some standardization across industry expectations would be great.

I admit here I have just start my search so perhaps I am being a bit lazy Smile | :)

But is IS 3:53 AM on Jan 1 and I am up thinking about THIS Smile | :)

so I figured I'd ask the masses here for some advice at the start of the new year..

Thanks for any pointers.

LinkedIn[^] | Blog[^] | Twitter[^]

Re: Looking for field name standards documents

Eddy Vluggen2-Jan-10 7:27

Using Stored Procedures as a field (SQL Server 2008)

2-Jan-10 7:27

Ray Cassick wrote:
Things that address naming conventions across various industry\business segments so that I can look to normalize a set of tables for a generalized system that could cross industry boundaries and still manage to keep some standardization across industry expectations would be great.

Probably not what you expected, but I'd go for English. No systems hungarian, just a short name in a single language. Now, a fieldname is rarely self-descriptive, so it would be a good idea to keep a list with the fieldnames along with their descriptions. That way you can make sure that everybody knows what you mean when you refer to a "client".

A good description should be self-explanatory to readers who have no specific knowledge of either domain. That requires a definition of the fields' contents in such a way that it's not going to be confusing to the reader, whether it's a programmer thinking about sockets or an enduser thinking about leads - it should be clear from the documentation of the design what a "client" is. Your goal would be to remove any possible ambiguity.

MSDN[^] would be a general pointer. It would also help if you use a standard-unit of measurement, where possible.

--edit--
SQL is case-insensitive, hence no recommendations on the use of camelCasing and the like Smile | :)

I are Troll Suspicious | :suss:

willempipi31-Dec-09 0:34

willempipi

31-Dec-09 0:34

Hi,

I have made a stored procedure witch returns a field that i want to use in a query. There is 1 parameter that I give to the stored procedure witch comes from the normal query. The info about the stored procedure:

Name: SP_Calculate
Input: ID
Output: table with 1 column: Amount and 0 or 1 row.

And this is what I want to do in the query:

SELECT 
db1.dbo.table1.ID as ID, 
db1.dbo.table1.Field1 as Field1, 
db2.dbo.table2.Field2 as Field2, 
( SELECT Amount FROM db1.dbo.SP_Calculate(db1.dbo.table1.ID) ) As Amount

FROM db1.dbo.table1

INNER JOIN db2.dbo.table2
ON db1.dbo.table1.ID = db2.dbo.table2.ID

Of course this code doesn't work, but i hope it gives you a good view of what I try to do. This query is a example query, the actual query is really long.

Re: Using Stored Procedures as a field (SQL Server 2008)

Corporal Agarn31-Dec-09 0:59

Corporal Agarn

31-Dec-09 0:59

If you are expecting one (or no) value why not use a user defined function? The function could return NULL for no value

Re: Using Stored Procedures as a field (SQL Server 2008)

willempipi31-Dec-09 1:30

willempipi

31-Dec-09 1:30

Could you give me an example how to use a function in my query? I have never used functions in sql server, only views/stored procedures and triggers. Converting my stored procedure to a function would not be the biggest problem, i think.

Re: Using Stored Procedures as a field (SQL Server 2008)

Corporal Agarn31-Dec-09 2:27

Corporal Agarn

31-Dec-09 2:27

A function is very similar to a stored procedure. If your stored procedure is db1.dbo.SP_Calculate(db1.dbo.table1.ID) the your function could be db1.dbo.udf_Calculate(db1.dbo.table1.ID)

See BOL for a complete explanation of CREATE FUNCTION (scalar).

USE DB1
GO
CREATE FUNCTION dbo.udf_Calculate  ( @inID VARCHAR(10))
RETURNS money 
AS
DECLARE @myout MONEY;
SELECT @myout = amount from mytable where ID = @inID;
RETURN(@myout);

This is a quick of the cuff example.

Good luck

Re: Using Stored Procedures as a field (SQL Server 2008)

willempipi31-Dec-09 2:17

willempipi

31-Dec-09 2:17

Hi,

I already got the solution, thanks to djj55:

I converted the Stored Procedure into a Database Function, now i can use it in the query like this:

SELECT 
db1.dbo.table1.ID as ID, 
db1.dbo.table1.Field1 as Field1, 
db2.dbo.table2.Field2 as Field2, 
db1.dbo.Calculate(db1.dbo.table1.ID) As Amount

FROM db1.dbo.table1

INNER JOIN db2.dbo.table2
ON db1.dbo.table1.ID = db2.dbo.table2.ID

Re: Using Stored Procedures as a field (SQL Server 2008)

Corporal Agarn31-Dec-09 2:29

Corporal Agarn

31-Dec-09 2:29

Glad you got it. It took me a while to type my last replay as I am at work. Smile | :)

SQL Server Simultaneous Update with WHERE EXISTS

c0der200930-Dec-09 22:14

c0der2009

30-Dec-09 22:14

This is a SQL Server query (I am using SQL Server 2008).

I have an application which runs jobs. I have a JOB table with columns JOB_ID, JOB_STEP etc. When a job is started, an entry is made in this table. When each step is completed, this entry is updated to reflect the next step in JOB_STEP column.

The application is a multi processing application, ie, there are multiple instances of the same application running on multiple servers. All of them read, insert and update into the same database table. (There is only one instance of the DB).

Now, I have a business requirement that no two jobs should be on step 20 (or step 100) together. Ie, if any one job is already on step 20 or 100, all other jobs should wait till that job step is completed.

I have a stored procedure which the application calls to update the step info. I tried something like

update JOB <br />
	set JOB_STEP = @nextStep<br />
	where<br />
	(<br />
		(@nextStep <> '20' AND @nextStep <> '100')<br />
		OR<br />
		(<br />
			(@nextStep = '20' OR @nextStep = '100')<br />
			AND<br />
			NOT EXISTS<br />
(<br />
			select * from LAUNCH_STATISTICS<br />
			where <br />
			(JOB_STEP = '20' OR JOB_STEP = '100')<br />
			and JOB_ID <> @jobId<br />
			)<br />
		)<br />
	)<br />
	and JOB_ID = @jobId;

But it didn’t work. If jobs try to change steps one after the other, this works. But if I call this SP from two different processes at the same time, both of them manage to end up with Step 20 (or 100) sometimes.
Please help me in finding out what I am missing.

I tried with different TRANSACTION_ISOLATION levels but it didn't help.

Re: SQL Server Simultaneous Update with WHERE EXISTS

c0der200930-Dec-09 23:51

c0der2009

30-Dec-09 23:51

In short,
Is UPDATE-WHERE-EXISTS able to handle concurrency? If not, then is there any way to get that done?

Connection string loaded from config file

Nigel Mackay30-Dec-09 18:44

Nigel Mackay

30-Dec-09 18:44

I have:

configMap =
   new ExeConfigurationFileMap { ExeConfigFilename = "ToolHistoryServer.exe.config" };
myCfg =
   ConfigurationManager.OpenMappedExeConfiguration(configMap, ConfigurationUserLevel.None);
dataSource = myCfg.AppSettings.Settings["DataSource"].Value;
cnStr = "Data Source='" + dataSource + "';" +
        "Trusted_Connection=true;" +
        "Database=ToolHistory";

Config file:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
   <appSettings>
      <add key="DataSource" value="NIGEL-PC\\SQLEXPRESS" />
   </appSettings>
   <system.runtime.remoting>
    <application>
      <channels>
        <channel ref="tcp" port="50050" />
      </channels>
    </application>
  </system.runtime.remoting>
</configuration>

I have tried cnStr with and without the single quotes, but neither work. Something is wrong with my syntax.
It compiles and runs, but data access fails. Can't debug because it is on the server side.

Re: Connection string loaded from config file

Eddy Vluggen31-Dec-09 4:59

Re: Connection string loaded from config file

31-Dec-09 4:59

Doesn't that give a connectionstring like below?

Data Source='NIGEL-PC\SQLEXPRESS'; 
Trusted_Connection = true; 
Database = ToolHistory;

What happens when you remove the quotes around the name of the datasource?

I are Troll Suspicious | :suss:

Nigel Mackay31-Dec-09 19:32

Nigel Mackay

31-Dec-09 19:32

I would have thought so. I have just written test code to return cnStr, and I find that dataSource is NIGELPC\\\\SQLEXPRESS!
Wrote a snippet to remove the extra \\ and now it works.

SQL Server 2005 Express edition Registration.

TinyDevices30-Dec-09 1:41

TinyDevices

30-Dec-09 1:41

Hi,
I'm trying to register SQL Server Express edition engine into Network. I've checked everything, but I'm not able to find it in the servers list. What are the things I should check more?
I check TCP/IP & Named pipes connections,
Enabled Remote connections,
*Restarted the service*
**Restarted the machine**!

But nothing works. May be that's what you call Express edition?

Andy_L_J30-Dec-09 3:23

Andy_L_J

30-Dec-09 3:23

Was it a default instance?

Should be ServerName\SQLEXPRESS from memory.

I don't speak Idiot - please talk slowly and clearly

'This space for rent'

Driven to the arms of Heineken by the wife

TinyDevices30-Dec-09 19:32

TinyDevices

30-Dec-09 19:32

Yes this is what is shown when I browse for SQL servers in the n/w through Management studio.
It gets displayed in the servers list in the local Mgmt studio. But when I open the studio from another machine, I'm not able to see. And it's vice-versa for the DB server that's hosted in the other machine. I'm not able to see it from my mgmt studio. But it gets displayed on his own mgmgt studio. You get it?

Eddy Vluggen30-Dec-09 6:04

30-Dec-09 6:04

ConquistadorEx wrote:
I'm trying to register SQL Server Express edition engine into Network.

You'd have to explain that a bit. Once installed, all should be fine. Where are you registering it? What application?

ConquistadorEx wrote:
What are the things I should check more?

Whether the SQL Browser service is running. Check MSDN[^]. Also, check your firewalls, and try to connect to the database using SQL Management Studio or ISql.exe.

I are Troll Suspicious | :suss:

TinyDevices30-Dec-09 19:34

TinyDevices

30-Dec-09 19:34

Please check my reply above. I'm just right clicking on the server instance displayed in the Mgmt Studio then selecting "Register".

Eddy Vluggen31-Dec-09 5:02