|
You may want to take a look at the sp_executesql system stored procedure: http://msdn.microsoft.com/en-us/library/ms188001.aspx[^]
This allows you execute the dynamic SQL string, as well as specify parameter definitions and parameter assignments that can bind local SQL variables to the dynamic SQL call.
DECLARE @InputParam int,
@OutputParam int
EXECUTE sp_executesql 'query',
N'@DynamicInputParam int, @DynamicOutputParam int OUTPUT',
@DynamicInputParam = @InputParam,
@DynamicOutputParam = @OutputParam OUTPUT
Within 'query', use the dynamic parameters/variables defined in the 2nd parameter (@DynamicInputParam and @DynamicOutputParam). Within 'query', these variables will initially contain the values of (@InputParam and @OutputParam). If a dynamic parameter is defined as an OUTPUT parameter (@DynamicOutputParam), any assignment to that variable will be reflected in the local variable (@OutputParam) after the call to sp_executesql.
|
|
|
|
|
hi peoples,
select * from table where lower(column) like lower('%test%')
this statement works fine for me, but
declare @abc char(50)
set @abc='%test%'
select * from table where lower(column) like lower(@abc)
its not working for me?
should i go for dynamic query though??
can any one help me?
thanks in advance and happy new year
|
|
|
|
|
Hema Bairavan wrote: its not working for me?
What error you are getting ?
Abhijit Jana | Codeproject MVP
Web Site : abhijitjana.net
Don't forget to click "Good Answer" on the post(s) that helped you.
|
|
|
|
|
abhijit,
i am not getting any error,
but the result s not correct, its not filtering the correct data from the database.and sorry for late reply.
|
|
|
|
|
Try this (I haven't tested)
DECLARE @ABC VARCHAR(50)
DECLARE @SQL VARCHAR(1000)
SET @ABC = '%test%'
SET @SQL = 'SELECT * FROM table WHERE lower(column) LIKE (''' + @ABC + ''')'
EXEC (@SQL)
I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|
|
thanks for response andy,
regarding dynamic query i can do,
but it will affect the perfomance, because it has to compile when ever you are running the select statement(as i m using the same in procedure)
my question is y cant a static one?
|
|
|
|
|
Haven't tried this, but noticed you are using the type char(50) instead of varchar(50). Is the char(50) adding padded characters to the end of the string, causing it to not match?
|
|
|
|
|
yes darren, thanks for your info.
me to not noticed the same. its working fine when i execute from the procedure from the SQL Query editor.
but if i run from the application (asp.net C#) using command.executereader(),
i m not getting the result, do you have any ideea?
|
|
|
|
|
Hi,
i want to insert some users in a table. before inserting those users i want to check that user is already exists or not. so i want to written one sp.can any one please help me by posting some sample Storedproc
fttyhtrhyfytrytrysetyetytesystryrty
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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
Niladri Biswas
|
|
|
|
|
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 But is IS 3:53 AM on Jan 1 and I am up thinking about THIS so I figured I'd ask the masses here for some advice at the start of the new year..
Thanks for any pointers.
|
|
|
|
|
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
I are Troll 
|
|
|
|
|
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.
|
|
|
|
|
If you are expecting one (or no) value why not use a user defined function? The function could return NULL for no value
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
Glad you got it. It took me a while to type my last replay as I am at work.
|
|
|
|
|
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.
|
|
|
|
|
In short,
Is UPDATE-WHERE-EXISTS able to handle concurrency? If not, then is there any way to get that done?
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
