|
Is there any way to define Optional paramter in SQL server SP.??
CREATE PROC [ EDURE ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
Thanks
Amit
|
|
|
|
|
Amit Kumar G wrote: Is there any way to define Optional paramter in SQL server SP.??
Yes, and you've already answered your question
Amit Kumar G wrote: [ = default ]
Here is an extract from the documentation, which you obviously have since you've quoted from it:
default
Is a default value for the parameter. If a default is defined, the procedure can be executed without specifying a value for that parameter. The default must be a constant or it can be NULL. It can include wildcard characters (%, _, [], and [^]) if the procedure uses the parameter with the LIKE keyword.
|
|
|
|
|
|
Hi,
To illustrate the problem, let's say we have those two tables:
table1
id cost
===============
1 100.15
2 577
3 214.975
4 894.12
table2
id cost
===============
1 217.13
2 577
3 214.975
4 214.975
I want to select all rows from both tables that don't have an equivalent value in the cost field .. for example, in table1 we have the value 100.15 which doesn't exist in table2 therefore we want it to show in the results, the tricky part here is that if you look at table2 you'll find that the value 214.975 exist twice while it only exists once in table1, I want it to show only once in the results.
To make it clearer, this is an accounting application, we have two accounts that should be identical, so we want to select all amounts from both accounts that have no equivalent in the other account.
Your help is really appreciated
Wal
|
|
|
|
|
check these queries:
for table1:
Select distinct cost<br />
from table1 t1<br />
where (Select count(*) from table2 t2 where t2.cost=t1.cost) <> (Select count(*) from table1 t11 where t11.cost=t1.cost)
results:
100.15
214.975
894.12
for table2:
Select distinct cost<br />
from table2 t2<br />
where (Select count(*) from table1 t1 where t1.cost=t2.cost) <> (Select count(*) from table2 t22 where t22.cost=t2.cost)
results:
214.975
217.13
note that these queries are not great from the performance point of view.
cursors can do the work too..
|
|
|
|
|
hspc wrote: check these queries:
for table1:
Select distinct cost
from table1 t1
where (Select count(*) from table2 t2 where t2.cost=t1.cost) <> (Select count(*) from table1 t11 where t11.cost=t1.cost)
results:
100.15
214.975
894.12
Thanks for your help, but well, actually this is not quite what I'm trying to do. In the results above, 214.975 shouldn't be showing as it already exists in table2, it should only show if it occurs in table1 more times than it occurs in table2 ... e.g. if it occurs 7 times in table1 and 4 times in table2 then it should show 3 times in the results, can that be achieved?
Wal
|
|
|
|
|
Hai,
In my product table
pr_id Pr_ty pro_name
1001 video film au
1002 audio film vd
1003 drinks cocacola
1004 video songs_vd
1005 audio songs_au
i have above records if i select pr_ty i want to display only 3 records like video,audio and drinks only ...
I am new in sql server please help me.....
A.Rajapandian
|
|
|
|
|
SELECT DISTINCT pr_ty FROM Product
|
|
|
|
|
Thanks for your help...
A.Rajapandian
|
|
|
|
|
I have a few fields I would like to have encrypted in the database that will be displayed on a web page but will also be accessed by windows application. What is the best encryption stategy. Use the encryption tools from SQL Server 2005, or the encryption toolset from ASP.net 2.0
Right now I have been playing with using SQL server but trying to understand how to open the keys when I need to decrypt something to display it on the page or on a report. One thought is to put it all in the stored procedure but I dont know what the performance hit would be for that.
Any help or good references would be appreciated.
Thanks.
DW
|
|
|
|
|
How about the System.Security.Cryptography namespace? That way the data is being transferred to the server in encrypted form.
Just a suggestion to consider, I'm by no means an authority on SQL (or ASP for that matter  ).
I have no idea what I just said. But my intentions were sincere.
|
|
|
|
|
Thanks for the reply.
I looked at that but my problem is that I need to be able to run reports using Crystal Reports along with display the information in a web page. So I dont know how to do it without building a separate application to run the CR.
It seems the simplest way to do that would be to use the SQL server encryption.
What do you think?
|
|
|
|
|
I have a table that looks something like this
ID Site_Code Item_Code Some_Other_Stuff....
=======================================================
1 AA 123 ...
2 DD 234 ...
3 GG 567 ...
4 QQ 111 ...
4 FF 123 ...
My problem is this, I need to retrieve all the rows within a group of Site_Codes (basically all the Site_Codes that relate to say, North America), but for each site the Item_Code may reoccur. So, Item "123" appears both in "AA" and "FF" but I only want to retrieve one copy of "123" for the group. To make things a little more complicated though, "123" in "AA" and "FF" may (although they are the same item) have different values in the "Some_Other_Stuff" fields, so I have to decide whether I want the "AA" copy or the "FF" copy of "123".
So, ultimately what I want to do is set a priority order the the site codes. So I'd retrieve all rows with Site_Code = "AA" and then all rows with Site_Code in ("DD","GG",...) that have Item_Codes not in "AA". I'd image I could do this with some creative table joining, but what would be the most efficient way to do this? The database is on Oracle and unfortunately I don't have the option to change the design.
Thanks
|
|
|
|
|
wouldn't something like this work?
Select A.Site_Code, A.Item_Code
FROM MyTable A
WHERE A.Item_Code NOT IN (Select Item_Code FROM MyTable WHERE Site_Code='AA')
|
|
|
|
|
Something like that would work. In particular I can do this:
Select A.*
FROM MyTable A
WHERE A.Item_Code NOT IN (Select Item_Code FROM MyTable WHERE Site_Code='AA') AND Site_Code in ('BB','CC','DD')
UNION
SELECT * FROM MyTable WHERE Site_Code='AA'
And that mostly works. I get all the records with Site_Code='AA' plus all the records in 'BB','CC' and 'DD' with Item_Codes not in 'AA'. The problem, of course, is that there may be a few Item_Codes in 'BB' and 'CC' but not in 'AA', which means I get duplicate records for that Item_Code. There aren't very many of them, but they do exist.
I assume I could fix the problem with something like this (not yet tested):
Select * FROM MyTable WHERE Site_Code='AA'
UNION
Select A.* FROM MyTable A WHERE A.Item_Code NOT IN
(Select Item_Code FROM MyTable WHERE Site_Code = 'AA') AND Site_Code='BB'
UNION
Select A.* FROM MyTable A WHERE A.Item_Code NOT IN
(Select Item_Code FROM MyTable WHERE Site_Code IN ('AA','BB')) AND Site_Code='CC'
UNION
Select A.* FROM MyTable A WHERE A.Item_Code NOT IN
(Select Item_Code FROM MyTable WHERE Site_Code IN ('AA','BB','CC')) AND Site_Code='DD'
...
But that just seems absolutely horrible. Is there an easier way?
|
|
|
|
|
Even though it's another horrible option, You could create a temporary table that contains all site codes that are less than the current one.
I really think the problem is due to poor database design than anything else.
|
|
|
|
|
ednrgc wrote: I really think the problem is due to poor database design than anything else.
And I absolutely wouldn't disagree. But I only have limited leverage to try and use against the infrastructure people to get anything done. I'm not even sure if I have permission to create a temporary table. 
I'll probably have to end up doing a hybrid of table joins to try and catch most of the duplicates and then maybe prune out the remaining ones in code by iterating through the whole dataset. There are only a few thousand records to deal with (22K in all sites, about 8+K for all US sites).
|
|
|
|
|
Dear All,
i wanted to know is it possible to debug stored procedure as we debug in dotnet. As i have created following stored procedure to save the records.When
i Exec it it does not show any error but it does not insert the record to corresponding table. Please guide me
Stored procedure is
CREATE PROCEDURE [dbo].[servchargsav]
@branch varchar(20),
@serv varchar(20),
@scharge varchar(20),
@cname varchar(50),
@ip varchar(20)
AS
if @branch is not null
begin
if @serv is not null
begin
declare serv_cursor cursor for
select servicecharge from branchwisecharge where branchid = @branch and serviceid =@serv
open serv_cursor
fetch next from serv_cursor
if @@fetch_status = 0
begin
insert into branchwiseservice(branchid,serviceid,servicecharge,bcreatedby , bcreateddt,ipadd) values(@branch,@serv,@scharge,@cname,getdate(),@ip )
end
else
begin
update branchwisecharge set servicecharge = @scharge, bmodifiedby = @cname, bmodifieddt = getdate() where branchid = @branch and serviceid =@serv
end
close serv_cursor
deallocate serv_cursor
end
end
GO
regards
imran khan
|
|
|
|
|
You can debug a stored procedure with Query Analyzer
Wout Louwers
|
|
|
|
|
Try this version instead, which doesn't use cursors:
CREATE PROCEDURE [dbo].[servchargsav]
@branch varchar(20),
@serv varchar(20),
@scharge varchar(20),
@cname varchar(50),
@ip varchar(20)
AS
if @branch is not null
begin
if @serv is not null
begin
SELECT @Count = COUNT(1) FROM branchwisecharge
WHERE branchid = @branch and serviceid =@serv
-- A count of zero means we can't find this record.
IF @Count = 0
BEGIN
INSERT INTO BranchWiseService(branchid, serviceid, servicecharge,
bcreatedby, bcreateddt,ipadd)
VALUES (@branch, @serv, @scharge, @cname, getdate(), @IP)
ELSE
UPDATE branchwisecharge set servicecharge = @scharge, bmodifiedby = @cname,
bmodifieddt = getdate() where branchid = @branch and serviceid =@serv
END
END
END
GO
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.
|
|
|
|
|
Thanks Pete, Thanks for your idea. I was unnessacesary using cursur
THanks once again.
regards
imran khan
|
|
|
|
|
That's a bit arcane. I'd use
IF EXISTS
( SELECT * FROM branchwisecharge
WHERE branchid = @branch AND serviceid = @serv )
BEGIN
INSERT ...
END
ELSE
BEGIN
UPDATE ...
ENDNote that IF/ELSE in T-SQL is like if/else in C: the IF only governs the immediately following statement, not everything up to the ELSE. If you want to put multiple statements after an IF, you must use a BEGIN/END block (analogous to {} in C). Stylistically, I always use a block after an IF for the same reason as in C - it can be easy to get confused about whether a statement is controlled by the IF or not.
|
|
|
|
|
I have a table with a bitmask value column and would like to select from this table where records have any match with another bitmask value.
For instance 3 database records:
ID, bitmask_value
1, 1
2, 3
3, 16
To check if a single bit is selected, I can:
SELECT * FROM table WHERE (bitmask_value & 1) = 1
(checks if bit 1 is selected - returns ID 1 and ID 2)
But I would like to get all records where a criteria bitmask value I define is selected, such as, user selects 1 and 2 = 3.
I could do this:
SELECT * FROM table
WHERE ((bitmask_value & 1) = 1 OR (bitmask_value & 2) = 2)
BUT if my criteria bitmask value includes several bits, can I query a different way to compare bitmask_value and criteria_bitmask, and get the proper results without checking each bit individually?
So given a criteria_bitmask value of 3... can I query using the value 3 somehow and get the following:
-any rows with 1 selected
-any rows with 2 selected
-any rows with 1 and 2 selected
-any rows with 1 and any others selected
-any rows with 2 and any others selected
-any rows with 1 and 2 and any others selected
I'm only filtering rows that DO NOT have a 1 or a 2 or both a 1 and a 2.
|
|
|
|
|
Sorry, some of your terminology is confusing me.
Leftyfarrell wrote: 1 and 2 = 3.
Actually 1 AND 2 = 0. Did you mean 1 OR 2?
Leftyfarrell wrote: SELECT * FROM table
WHERE ((bitmask_value & 1) = 1 OR (bitmask_value & 2) = 2)
What about:
WHERE (bitmask_value & 3) <> 0
bitmask_value result
0 0
1 1
2 2
3 3
4 0
5 1
6 2
7 3
8 0
etc.
|
|
|
|
|
I did mean 1 OR 2 selected. By saying 1 and 2 = 3, I meant that the search criteria is defined for me by a user selecting options, and if they select options 1 and 2, I need to pull anyone that matches 1 OR 2. Their interface assumes an OR condition on the options they select.
I believe what you have provided is what I need. I just need to test a bit more.
Thank-you.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
