-- New Table Structure CREATE TABLE dbo.FirstTable( FirstField varchar(100) null ) --Table Rename -- exec sp_rename 'dbo.FirstTable', 'dbo.TableRenamed' -- Columns Rename exec sp_rename 'dbo.FirstTable.FirstField', 'FieldRenamed' /* as a note, instead of using sp_rename to rename COLUMNS an alternative method is to use the ALTER table command to change a column's data type or (to re-name it)a new column can be added and old one dropped */ alter table dbo.TableRenamed add FieldRenamedSecondTime varchar(100) NULL alter table dbo.TableRenamed drop column FieldRenamed varchar(100) NULL -- To just change the datatype: alter table dbo.TableRenamed alter column FieldRenamed int NULL
Posts Tagged ‘how to’
Posted in Database, TSQL, tagged alternative, change, column, Convert, edit, example, How, how to, howto, look, modify, restructure, sample, sql, structure, table, to, TSQL, Usama on February 11, 2013| Leave a Comment »
Posted in Computer, Database, Explanations, How To, TSQL, tagged alternative, cartesian, CROSS, explanation, how to, howto, join, Product, sql, TSQL, Usama, way on December 13, 2012| Leave a Comment »
/* Cross Join: Each row in the first table is paired with all the rows in the second table. */ if OBJECT_ID('tempdb..#a') is not null drop table #a if OBJECT_ID('tempdb..#b') is not null drop table #b create table #a ( acol varchar(10) ) insert into #a values ('a1'), ('a2') create table #b ( bcol varchar(10) ) insert into #b values ('b1'), ('b2') select * from #a select * from #b select * from #a cross join #b
Command like Sql can be used to run sql scripts saved in disks. Therefroe if there is a bunch of scripts
(files) they can all be added with ttheir path and file name in notepad and have it saved with anyname with a
cmd extension. Then running this file would execute all the lines in the file.
Use “sqlcmd” instead of osql because “osql” cannot handle alot of special characters like the “£” sign.
Here is an example:
sqlcmd -S MyServerName -E -d My_dbName -i “C:\MyTSQL\MyScript.sql”
The above if run in commandline will execute the script in the path.
This is usefull when we need to run batches of scripts.
To make scripts upload/commit to a server by using the above command make sure the SP/Function or other
objects are alwasy dropped and recreated e.i. starts with the create statement as below:
--For SP: --------- IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mySP]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[mySP]] GO CREATE PROCEDURE [dbo].[mySP] @var AS VARCHAR(100) as begin ... end --For Function: -------------- IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[myUDF]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) DROP FUNCTION [dbo].[myUDF] GO create FUNCTION [dbo].[myUDF] ( @var AS VARCHAR(100) ) RETURNS INT AS BEGIN ... end
Posted in Computer, Database, Explanations, How To, TSQL, tagged alternative, Binary, Bitwise, example, explanation, how to, howto, mask, Masking, sample, sql, TSQL, Usama, way on December 12, 2012| Leave a Comment »
Assume the below scenario:
A system checks a stock level for a grocery store that sells 3 items and whenever a certain item runs out it flags up a message.
Say the 3 items are:
To make use of binary masking (also known as bitwise) to be able to track which needs to be highlighted, we need to do the following:
1) We will assign a binary number to each one of the item, 1 for denoting we need to flag a message and 0 f0r doing nothing. Each of the the items in turn will have a fixed position. So in this instance there are three position for three items and we can assign it as below:
Item Position Binary Value
—– ——— ————-
Mango 0 1
Orange 1 0
Apple 2 1
2) Now, we need a single decimal number that represents which items jave a binary 1 to flag a message and which don’t. To do that we use the Position value and treat that as the power of 2.
So, we use Position 1 and 3 (because those have the binary value 1 binded) and it should equate to:
(2^0) = 1
+ (2^2) = 4
Total = 5
3) The value 5 can then be sent to the section of the system which flags messages. It should then have a script to reverse it back to a binary number which is 101.
The position number is fixed and therefore can be easily be deduced as to which binary number is binded to whcih item.
This way by only sending an integer number we can for a group of item attached and interchagne flag data.
>Returns the binary representation of a number:
-- courtesy to a script somewhere in the internet CREATE FUNCTION [csrt].[udf_ReturnBinaryPattern] ( @Byte INT ) RETURNS CHAR(8) AS BEGIN DECLARE @Pattern CHAR(8) SET @Pattern = '' SELECT @Pattern = convert(VARCHAR,+(@Byte & 1) / 1) + convert(VARCHAR,(@Byte & 2) / 2) + convert(VARCHAR,(@Byte & 4) / 4) + convert(VARCHAR,(@Byte & 8) / 8) + convert(VARCHAR,(@Byte & 16) / 16) + convert(VARCHAR,(@Byte & 32) / 32) + convert(VARCHAR,(@Byte & 64) / 64) + convert(VARCHAR,(@Byte & 128) / 128) RETURN (@Pattern) END GO
>Optional – Explaning & bitwise AND operator:
The binary representation of 170 (a_int_value or A) is 0000 0000 1010 1010. The binary representation of 75 (b_int_value or B) is 0000 0000 0100 1011. Performing the bitwise AND operation on these two values produces the binary result 0000 0000 0000 1010, which is decimal 10.
SELECT 170 & 75
Result is 10 (thisis a decimal number)
The & operator tranforms number or charters into binary and then adds them up:
170 = 0000 0000 1010 1010
75= 0000 0000 0100 1011
10 = 0000 0000 0000 1010
Another artice: http://sqlfool.com/2009/02/bitwise-operations/
SSRS does not allow parameters to dynamically hide and unhide (unless asp scripting/xml/url changing/other scripting outside BIDS is implemented).
A workaround around could be to keep all the parameters visible and instead of trying to hide the ones not needed just make a single value available.
Let me elaborate:
1) @Par [text type] > the parameter you want the the dynamic effect applied to it
2) “usp_dp” > if the parameter is a multi valued then lets say this is the name of the SP it generates the values from. This SP also needs to have a parameter of its own that will take an integer value input. Lets call it @ID.
3) @Switch [text type] > if this is equal “A” then you want to use @Par else not.
You will need:
1) Another paramter (either existing or a new one) that accepts integer. I usually use an existing parameter that accepts a certain ID number. For this example lets say we call it @ID (this parameters is of same tyep and name as in usp_dp).
2) On BIDS 2008 on the “Report Parameter Properties” goto the “Advanced” tab and select “Always refresh” or do the same for any other versions. – This step is optional but recommended.
We will first consider that its a multi-valued parameter:
1) Goto the “Dataset Properties” of “usp_dp” and then to “Parameters” and on for @ID Parameter Name put the below “Parameter Value:
What the above does is:
Depending on the value of @ID it will generate the list available for @Par to display. So if you don’t want to display any values you have to say that @ID = 0.
Finally what you need to do is write your “usp_dp” script in such a way that when ever @ID =0 the script should return “N/A” else it will return the normal values for the parameters.
I am sure if you have worked out the multivalued parameter then working out how to apply a similar effect to a textbox parameter (where you need to type in your value) will not be a problem. My suggestion is to use the “Default Values” option in the “Report Parameter Properties”.
declare @passed varchar(max) set @passed = 'FirstBit exec dbo.usp_Sites 4' print substring(@passed, 1, -- Change 1 to the vlaue you want the extraction to start from patindex('% exec %', @passed) - 1 -- Chage " exec " to any value in the string till which you want it extracted ) print substring(@passed, patindex('% exec %', @passed) + 1, len(@passed) - patindex('% exec %', @passed) ) -- The function Left() and Right() are also used when you want the extraction to start from either -- left most or right most side.