Database

Jörgen Andersson wrote:

Pyramid schemes are illegal in a larger part of the world, and I'm not being part of it!

Where ((Isnull(@UserName,'') == '' OR Table.UserName like @Username)
    and ((Isnull(@Sate,'') == '' OR Table.State like @State)

SELECT s.salary, e.ename FROM employees e INNER JOIN salaries s ON s.pid = e.pid HAVING s.salary = MAX(s.salary)

Maciej Los wrote:

too many subqueries.

ALTER PROCEDURE [dbo].[UpdateSurvey]
    -- Add the parameters for the stored procedure here
    @ID int,@response nvarchar(50),@buildingpoint  nvarchar(50),@ID1 int
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

if(@ID1=1)
Begin
UPDATE survey  SET
 ques_1 = (select response from surveyanswers t2 where id=@response),q1buildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
End

Else If(@ID1=2)

Begin
UPDATE survey  SET
 ques_2a = (select response from surveyanswers t2 where id=@response),q2abuildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
End

Else If(@ID1=3)

Begin
UPDATE survey  SET
 ques_2b = (select response from surveyanswers t2 where id=@response),q2bbuildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
END
Else If(@ID1=4)

Begin

UPDATE survey  SET
 ques_2c = (select response from surveyanswers t2 where id=@response),q2cbuildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID

END
Else If(@ID1=5)

Begin


UPDATE survey  SET
 ques_3 = (select response from surveyanswers t2 where id=@response),q3buildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
END

Else If(@ID1=6)

Begin
UPDATE survey  SET
 ques_4 = (select response from surveyanswers t2 where id=@response),q4buildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID

END
Else If(@ID1=7)

Begin
UPDATE survey  SET
 ques_5 = (select response from surveyanswers t2 where id=@response),q5buildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID

END
Else If(@ID1=8)

Begin
UPDATE survey  SET
 ques_6 = (select response from surveyanswers t2 where id=@response),q6buildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID
   END

Else If(@ID1=9)

Begin
UPDATE survey  SET
 ques_7 = (select response from surveyanswers t2 where id=@response),q7buildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID

END
Else If(@ID1=10)

Begin
UPDATE survey  SET
 ques_8 = (select response from surveyanswers t2 where id=@response),q8buildpoint=(select buildingpoint from surveyanswers t2 where id=@buildingpoint)
from survey c
inner join surveyanswers s
on s.id = c.cid
where c.cid = @ID


output


exec UpdateSurvey 5,5,5,2(id,response,buildingpoint,@ID1=2)
but instead of updating @ID1=2 ,it updates @ID1=5 .BUT I want to update @ID1=2 query .Is there any solution for this

ALTER TABLE dbo.mytable
ADD CONSTRAINT DF_mytable_VendorLevelId DEFAULT (0) FOR VendorLevelId;

UPDATE dbo.mytable
SET VendorLevelId = 0
WHERE VendorLevelId Is Null;

ALTER TABLE dbo.mytable
ALTER COLUMN VendorLevelId int NOT NULL;

CREATE TABLE dbo.doc_exy (column_a INT )  ;
GO

INSERT INTO dbo.doc_exy (column_a) VALUES (10) ;
GO

SELECT * FROM  dbo.doc_exy 

10

ALTER TABLE dbo.doc_exy ALTER COLUMN column_a DECIMAL (5, 2);
GO

SELECT * FROM dbo.doc_exy

10.00

DROP TABLE dbo.doc_exy ;
GO